For the benefit of other readers, the two tables are ...
chargback transaction
id Outlet charg_amt Network Paiement_Id pp Montant
--------------------------------- -------------------------
1 222 220 V 1 222 232
2 222 234 M 2 222 32
3 23 34 V 4 343 54
4 222 34 v
and the desired result is ...
id Outlet charg_amt pp Montant Network res
--------------------------------------------------------------------
1 222 488 222 264 v 488/264)*100
Apparently you made a typing error in your problem description: 264=232+32, not 232+23, so I made that correction.
The id and network values in your desired result show one value of the row being summed; it's meaningless and misleading. To show all such values for that aggregating row, use Group_Concat().
Aggregating across joins induces spurious multiplications, we have to aggregate the tables in subqueries, then join those subqueries:
select a.outlet, a.Charges, b.Montant, format(100*Charges/Montant,2) as Res, a.Networks
from (
select outlet, group_concat(network) as Networks, sum(charg_amt) as Charges
from chargback
group by outlet
) a
join (
select pp, sum(montant) as Montant
from transaction
group by pp
) b on a.outlet=b.pp
where a.outlet=222;
+--------+---------+---------+--------+----------+
| outlet | Charges | Montant | Res | Networks |
+--------+---------+---------+--------+----------+
| 222 | 488 | 264 | 184.85 | V,M,v |
+--------+---------+---------+--------+----------+
Edited 1 time(s). Last edit at 03/27/2014 11:10AM by Peter Brawley.