Member Avatar for _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
  • 3 Contributors
  • 6 Replies
  • 168 Views
  • 5 Days Discussion Span
  • Latest Post Latest Post by _taz_

Recommended Answers

All 6 Replies

Member Avatar for urtrivedi

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

Edited by urtrivedi because: n/a
Member Avatar for _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)

Member Avatar for huangzhi

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

Member Avatar for _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

Member Avatar for 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
Member Avatar for _taz_

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

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.