SUM() Problem in LEFT OUTER JOIN

Transact-SQL
1

Here are my 3 Tables
Registration
CustCode CustName
1 a
2 b
3 c
RECEIPTS
RNo CustCode Amount
1 1 1000
2 1 40000
3 1 1000
4 2 5000

COMMS
RNo CustCode Amount CommAmt AgentID
1 1 1000 100 1
1 1 1000 80 2
2 1 40000 4000 1
2 1 40000 3200 2
3 1 1000 100 1
3 1 1000 80 2
4 2 5000 500 8

Now I,ve to Fetch Records for a particular CustCode and the sum of Receipts and sum of commission
for that I wrote

Select CustName, SUM(Receipts.Amount) AS PaidAmt , SUM(Comm.CommAmt) AS CommAmt
from Registration
LEFT OUTER JOIN Receipts ON Registration.CustCode = Receipts.CustCode
LEFT OUTER JOIN Comms ON Registration.CustCode = Comms.CustCode
Where REGISTRATION.CustCode =1
GROUP BY Registration.CustCode, CustName

But it gives wrong answer. What is the correct way to do the sum

2

Give us a little more detail on what that "wrong answer" is. Maybe an example of what it's populating versus what you want it to show.

3

In SUM(Receipts.Amount) it should Show 42000 but it's showing some wrong value
Also SUM(Comm.CommAmt) Also giving wrong result

4

You probably should join on the RNo as well, like shown below:

SELECT  CustName ,
        SUM(Receipts.Amount) AS PaidAmt ,
        SUM(Comm.CommAmt) AS CommAmt
FROM    Registration
        LEFT OUTER JOIN Receipts ON Registration.CustCode = Receipts.CustCode
        LEFT OUTER JOIN Comms ON Registration.CustCode = Comms.CustCode
			AND Receipts.RNo = Comms.RNo  ---<----- THIS
WHERE   REGISTRATION.CustCode = 1
GROUP BY Registration.CustCode ,
        CustName
5

Now it corrected the Sum(Comm.CommAmt)
But Sum(Receipts.Amount) gives double values
instead of 42000 it is showing 84000

6

The data you posted for Receipts for CustCode = 1 is the following, which does add up to 84,000. Is that data correct? Perhaps it got inserted twice?

RNo CustCode Amount CommAmt AgentID
1 1 1000 100 1
1 1 1000 80 2
2 1 40000 4000 1
2 1 40000 3200 2
3 1 1000 100 1
3 1 1000 80 2
7

No it's not inserted Twice. It has only three rows 1000 + 40000 + 1000 for custcode = 1

8

See if the following gives you better results?

SELECT  rg.CustName ,
        r.PaidAmt ,
        c.CommAmt
FROM    Registration rg
		OUTER APPLY
		(
			SELECT SUM(r.Amount) AS PaidAmt
			FROM Receipts r
			WHERE rg.CustCode = r.CustCode
		) r
		OUTER APPLY
		(
			SELECT SUM(c.CommAmt) AS CommAmt
			FROM Comms c
			WHERE rg.CustCode = c.CustCode
		) c
WHERE   rg.CustCode = 1
1 Like
9

Thanking You JamesK
It solved my issue.
I thought that I've to write some function to fetch the sum(Comm.CommAmt)

Again Thanking You!