LEFT OUTER JOIN WITH SUM - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

LEFT OUTER JOIN WITH SUM

Author

Topic

ravininave
Posting Yak Master

111 Posts

Posted - 2015-05-05 : 16:04:16

Hello, I'm trying this query


SELECT  P.AppMstID, A.AppMstName, SUM(COALESCE(P.AMOUNT,0)) AS PAYOUT,  SUM(COALESCE(E.AMOUNT ,0)) AS PAID  FROM PayReports P
INNER JOIN AppMast A ON P.AppMstID= A.AppMstID
LEFT OUTER JOIN IncExpMast as E ON P.AppMstID= E.AppMstID
GROUP BY  P.AppMstID, A.AppMstName

But I'm getting wrong sum in Paid Column. Why?

VB6/ASP.NET
------------------------
http://www.nehasoftec.com

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-05-05 : 16:12:49

Please provide sample data and expected output.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

ravininave
Posting Yak Master

111 Posts

Posted - 2015-05-05 : 16:20:59

Payreports Table
AppMstID Amount
1 200.00
1 96.00
1 200.00
1 96.00
1 200.00

AppMast

AppMstID AppMstName
1 Rajkumar

IncExpMast

AppMstID Amount
1 1000

But the output I'm getting is
AppMstID AppMstName PAYOUT PAID
1 Rajkumar 792 5000

Paid should be 1000

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-05-05 : 16:27:19

Show us the data from this:

SELECT P.AppMstID, A.AppMstName, P.AMOUNT, E.AMOUNT
FROM PayReports P
INNER JOIN AppMast A ON P.AppMstID= A.AppMstID
LEFT OUTER JOIN IncExpMast as E ON P.AppMstID= E.AppMstID
WHERE P.AppMstID= 1

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

ravininave
Posting Yak Master

111 Posts

Posted - 2015-05-05 : 16:29:18

AppMstID AppMstName AMOUNT AMOUNT
1 Rajkumar 200.00 1000
1 Rajkumar 96.00 1000
1 Rajkumar 200.00 1000
1 Rajkumar 96.00 1000
1 Rajkumar 200.00 1000

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-05-05 : 16:39:27

quote:
Originally posted by ravininave

AppMstID AppMstName AMOUNT AMOUNT
1 Rajkumar 200.00 1000
1 Rajkumar 96.00 1000
1 Rajkumar 200.00 1000
1 Rajkumar 96.00 1000
1 Rajkumar 200.00 1000

That's why. You have multiple rows in IncExpMast that match, so it's totaling up the 1000 5 times.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

ravininave
Posting Yak Master

111 Posts

Posted - 2015-05-05 : 16:42:13

I've multiple rows (5) in Payreports and only 1 row in IncExpMast
So, It's showing total upto 5 times. Then what is the solution.?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-05-05 : 16:48:44

Sorry that's what I meant.

WITH E (AppMstID, PAID)
AS
(
SELECT AppMstID, SUM(COALESCE(AMOUNT,0)) AS PAID
FROM IncExpMast
GROUP BY AppMstID
)
SELECT P.AppMstID, A.AppMstName, SUM(COALESCE(P.AMOUNT,0)) AS PAYOUT, E.PAID
FROM PayReports P
INNER JOIN AppMast A ON P.AppMstID= A.AppMstID
LEFT OUTER JOIN E ON P.AppMstID= E.AppMstID
GROUP BY P.AppMstID, A.AppMstName, PAID

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

ravininave
Posting Yak Master

111 Posts

Posted - 2015-05-05 : 16:54:01

Yes, it works now. Thanking You!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-05-05 : 17:21:46

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources