joining 3 tables messes up SUMS

[_taz_]

I have a query that works, I wanted to add another sum from another table, but when I do, the all the SUM values are wrong
this works

SELECT     Clients.ClientID, Clients.WholeName, 
SUM(CASE WHEN Payments.Creditorid = 0 THEN Payments.Amount ELSE 0.00 END) AS 'Admin Fees', 
SUM(CASE WHEN (Payments.Creditorid = 3) THEN Payments.amount ELSE 0.00 END) AS 'Retainer Fees', 
SUM(CASE WHEN (Payments.Creditorid = 4) THEN Payments.amount ELSE 0.00 END) AS 'Finance Fees', 
SUM(CASE WHEN (Payments.Creditorid = 2) THEN Payments.amount ELSE 0.00 END) AS 'NSF Fees', 
SUM(CASE WHEN Payments.bankaccountid = 0 THEN Payments.amount ELSE 0.00 END) AS Settlements                   
FROM         Clients LEFT OUTER JOIN
                      Payments ON Clients.ClientID = Payments.ClientID
WHERE     (Clients.ClientID = 1126)
GROUP BY Clients.ClientID, Clients.WholeName
ORDER BY Clients.ClientID

this does not

SELECT     Clients.ClientID, Clients.WholeName, 
SUM(CASE WHEN Payments.Creditorid = 0 THEN Payments.Amount ELSE 0.00 END) AS 'Admin Fees', 
SUM(CASE WHEN (Payments.Creditorid = 3) THEN Payments.amount ELSE 0.00 END) AS 'Retainer Fees', 
SUM(CASE WHEN (Payments.Creditorid = 4) THEN Payments.amount ELSE 0.00 END) AS 'Finance Fees', 
SUM(CASE WHEN (Payments.Creditorid = 2) THEN Payments.amount ELSE 0.00 END) AS 'NSF Fees', 
SUM(CASE WHEN Payments.bankaccountid = 0 THEN Payments.amount ELSE 0.00 END) AS Settlements,
 SUM(Receipts.ReceiptAmount) AS 'Total Receipts'
FROM         Clients LEFT OUTER JOIN
                      Receipts ON Clients.ClientID = Receipts.ClientID LEFT OUTER JOIN
                      Payments ON Clients.ClientID = Payments.ClientID
WHERE     (Clients.ClientID = 1126)
GROUP BY Clients.ClientID, Clients.WholeName
ORDER BY Clients.ClientID

[urtrivedi]

what is the actual problem, Your query is giving error or giving unexpected result

[_taz_]

what is the actual problem, Your query is giving error or giving unexpected result

the first query runs correctly returning
Admin fees: 624.00
retainer fees: 1585.95
finance fees: 2996.50
NSF fees: 0.00
Settlements: 3871.66

when I run the second query i get
Admin fees: 10608.00
Retainer Fees: 26961.15
Finance Fees: 50940.50
NSF Fees: 0.00
Settlements: 65818.22
Total Receipts: 344968.18

(Total Receipts should be 9039.11)

[huangzhi]

I think there are data double in table Receipts.
Please give some data for example.

[_taz_]

I think there are data double in table Receipts.
Please give some data for example.

I can assure you there is no data double in the receipts table, besides even if there were double that should affect the calculations in the other table. If everything in the receipts table (9,039.11)were doubled it would not add up to 344,968.18

[huangzhi]

I think there are some (ClientID = 1126) in table Receipts.
Change your SQL to:

SELECT	Clients.ClientID, Clients.WholeName, 
	SUM(CASE WHEN Payments.Creditorid = 0 THEN Payments.Amount ELSE 0.00 END) AS 'Admin Fees', 
	SUM(CASE WHEN (Payments.Creditorid = 3) THEN Payments.amount ELSE 0.00 END) AS 'Retainer Fees', 
	SUM(CASE WHEN (Payments.Creditorid = 4) THEN Payments.amount ELSE 0.00 END) AS 'Finance Fees', 
	SUM(CASE WHEN (Payments.Creditorid = 2) THEN Payments.amount ELSE 0.00 END) AS 'NSF Fees', 
	SUM(CASE WHEN Payments.bankaccountid = 0 THEN Payments.amount ELSE 0.00 END) AS Settlements,
	isNull((select SUM(Receipts.ReceiptAmount) from Receipts where Receipts.ClientID = Clients.ClientID), 0) AS 'Total Receipts'
  FROM	Clients --LEFT JOIN Receipts ON 
	--Clients.ClientID = Receipts.ClientID 
	LEFT JOIN Payments ON 
	Clients.ClientID = Payments.ClientID
  WHERE (Clients.ClientID = 1126)
  GROUP BY Clients.ClientID, Clients.WholeName
  ORDER BY Clients.ClientID

[_taz_]

Ok, that fixed it, I had ended up doing derived tables to get it to work, but your way is much cleaner.
thanks!