mysql sum() return double value using multiple joins

Question

select 
a.ClientID,
f.Currency,
a.OrganizationName,
COALESCE(sum(b.GrandTotal),0) as SaleGrandTotal,
COALESCE(sum(g.AmountReceived),0) as AmountReceived,
COALESCE(sum(b.GrandTotal - g.AmountReceived),0) as SaleBalanceRemaining, 
COALESCE(sum(d.GrandTotal), 0) as PurchaseGrandTotal, 
COALESCE(sum(e.AmountPaid), 0) as AmountPaid,
COALESCE(sum(d.GrandTotal - e.AmountPaid),0) as PurchaseBalanceRemaining,
COALESCE(sum(b.GrandTotal - g.AmountReceived),0) - COALESCE(sum(d.GrandTotal - e.AmountPaid),0) as Total 
from na_clients as a
join na_currency as f 

left join na_transaction as b
on a.ClientID = b.ClientID and b.CurrencyID = f.CurrencyID and b.IsActive = 1

left join na_recoverylogs as g
on b.TID = g.TID

left join na_purchase as d 
on a.ClientID = d.ClientID and d.CurrencyID = f.CurrencyID  and d.IsActive = 1

left join na_purchaselogs as e
on e.PID = d.PID

group by a.OrganizationName,f.Currency
order by a.OrganizationName

I am using multiple currency like dollar,CNY,rupees. It was working fine but today i noticed sum() double value like b.GrandTotal should be 11500 but its return 23000

Table Client:
clientid,name,organizationName
1,client1,OrgName
2,client2,OrgName

Table Currency:
currencyid,cname
1,Dollar
2,Rupees

Table Transaction:
tid,clientid,currencyid,grandTotal,amountReceived,balanceremaining
1,1,1,11000,0,11000
2,1,1,500,0,500

Table recoveryLogs: // Another Error Here
id,tid,amountreceived
1,1,0
2,2,0
3,2,2000     // Again sum() multiply value - because of PID 2 is repeating


Table Purchase:
pid,clientid,currencyid,grandTotal,amountPaid,balanceRemaining
1,1,1,25000,0,25000
1,2,2,2,3000,1000,2000
Now I am using sum(b.grandTotal) instead of 11500 it return 23000

Table PurchaseLogs: // Another Error Here
id,pid,amountpaid
1,1,0
2,2,1000
3,1,1000            // Again sum() multiply value - because of PID 1 is repeating

So result should be:

Client: Client1
SaleGrandTotal: 11500
AmountReceived: 0
SaleBalanceRemaining: 11500
PurchaseGrandTotal: 25000
AmountPaid: 0
PurchaseBalanceRemaining: 25000
Total Amount: -13500

But result i get:

    Client: Client1
    SaleGrandTotal: 23000
    AmountReceived: 0
    SaleBalanceRemaining: 23000
    PurchaseGrandTotal: 50000
    AmountPaid: 0
    PurchaseBalanceRemaining: 50000
    Total Amount: -27000

If i remove purchase clause(d and e) or transaction(b and g) clause from query it's working fine individually.

Answer 1

The reason data is doubling is your ClientID has different occurrences in Transaction and Purchase tables and hence not a 1-to-1 match. ClientID = 1 and CurrencyID = 1 appears twice in Transaction and only once in Purchase. When you join the tables, a combination set of 1 x 2 = 2 ClientID records result with some fields repeating data. Thus, summing will double for repeat entries. As illustration:

      Transaction Data    | Purchase Data
row1: 1,1,1,11000,0,11000 | 1,1,1,25000,0,25000 
row2: 2,1,1,500,0,500     | 1,1,1,25000,0,25000

Consider separating the aggregation between both tables using derived tables. Then, join the four underlying aggregates (transaction, purchase, recovery log, purchase log) for final query. The join will match 1-to-1 if you aggregate, grouping on ClientID and CurrencyID, TID and PID.

SELECT
  transAgg.ClientID, transAgg.Currency, transAgg.OrganizationName,
  transAgg.SaleGrandTotal, recovLogAgg.SumOfAmtReceived, 
  (transAgg.SaleGrandTotal - recovLogAgg.SumOfAmtReceived) as SaleBalanceRemaining, 
  purchAgg.PurchaseGrandTotal, purchLogAgg.SumOfAmtPaid, 
  (purchAgg.PurchaseGrandTotal - purchLogAgg.SumOfAmtPaid) as PurchaseBalanceRemaining,
  ((transAgg.SaleGrandTotal - recovLogAgg.SumOfAmtReceived) - 
   (purchAgg.PurchaseGrandTotal - purchLogAgg.SumOfAmtPaid)) As [Total]

FROM
  (SELECT
      a.ClientID, f.CurrencyID, f.Currency, a.OrganizationName,
      COALESCE(sum(b.GrandTotal),0) as SaleGrandTotal
  FROM na_clients as a
  INNER JOIN na_currency as f     
  LEFT JOIN na_transaction as b
       ON a.ClientID = b.ClientID 
       AND b.CurrencyID = f.CurrencyID 
       AND b.IsActive = 1              
  GROUP BY a.ClientID, a.OrganizationName, f.CurrencyID, f.Currency
  ORDER BY a.OrganizationName) As transAgg    

INNER JOIN

  (SELECT
      a.ClientID, f.CurrencyID, f.Currency, a.OrganizationName,
      COALESCE(sum(d.GrandTotal), 0) as PurchaseGrandTotal
  FROM na_clients as a
  INNER JOIN na_currency as f     
  LEFT JOIN na_purchase as d 
       ON a.ClientID = d.ClientID 
       AND d.CurrencyID = f.CurrencyID  
       AND d.IsActive = 1          
  GROUP BY a.ClientID, a.OrganizationName, f.CurrencyID, f.Currency
  ORDER BY a.OrganizationName) As purchAgg  

ON transAgg.ClientID = purchAgg.ClientID 
AND transAgg.CurrencyID = purchAgg.CurrencyID

INNER JOIN

 (SELECT
      g.TID, COALESCE(sum(g.AmountReceived),0) As SumOfAmtReceived
  FROM na_recoverylogs as g          
  GROUP BY g.TID) As recovlogAgg  

ON transAgg.TID = recovlogAgg.TID

INNER JOIN

 (SELECT
      e.PID, COALESCE(sum(e.AmountPaid),0) As SumOfAmtPaid
  FROM na_purchaselogs as e 
  GROUP BY e.PID) As purchlogAgg

ON purchAgg.PID = purchlogAgg.PID