SUM with Inner Joins - 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

General SQL Server Forums

New to SQL Server Programming

SUM with Inner Joins

Author

Topic

petenuffer
Starting Member

6 Posts

Posted - 2008-04-15 : 14:40:27

Heya -
Very new here to SQL, but when I do this following query

SELECT ProjMgr, SUM(Fee) AS Fee
FROM dbo.PR
WHERE (ProjMgr = '00138')
GROUP BY ProjMgr

I return these results:

ProjMgr Fee
00138 9145297

Simple enough, so I flex my newbie SQL muscles and put in the last name instead of the ProjectManager's employee number

SELECT dbo.EM.LastName, SUM(dbo.PR.Fee) AS Fee
FROM dbo.PR INNER JOIN
dbo.EM ON dbo.PR.ProjMgr = dbo.EM.Employee
WHERE (dbo.PR.ProjMgr = '00138')
GROUP BY dbo.EM.LastName

And get these results

LastName Fee
Boulet 9145297

Okay, NOW I'm getting cocky. I try to bring in an amount indicating held labor from another table that will have multiple instances of the same number, called WBS1, and show a sum of values contained over months of history in a sum. Before I do that, I add the table, and then I'm stuck - look what happens to my fee column...any ideas?

SELECT dbo.EM.LastName, SUM(dbo.PR.Fee) AS Fee
FROM dbo.PR INNER JOIN
dbo.EM ON dbo.PR.ProjMgr = dbo.EM.Employee INNER JOIN
dbo.LD ON dbo.PR.WBS1 = dbo.LD.WBS1
WHERE (dbo.PR.ProjMgr = '00138')
GROUP BY dbo.EM.LastName

RESULT

LastName Fee
Boulet 5371502314

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-04-15 : 14:42:44

Are there duplicates for LD.WBS1?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

petenuffer
Starting Member

6 Posts

Posted - 2008-04-15 : 16:01:15

Yes, there are multiple entries of WBS1

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-04-15 : 16:16:01

That's the problen then. Either add a column from LD to the GROUP BY to make the data unique or post some sample data to illustrate the duplicates.

If there isn't much data returned, you can post the data from here:
SELECT EM.LastName, LD.*
FROM dbo.PR INNER JOIN
dbo.EM ON dbo.PR.ProjMgr = dbo.EM.Employee INNER JOIN
dbo.LD ON dbo.PR.WBS1 = dbo.LD.WBS1
WHERE (dbo.PR.ProjMgr = '00138')

Mangle the data as needed if the data is sensitive. But make sure it illustrate your issue.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-15 : 16:17:24

Also see
http://www.sqlteam.com/article/how-to-use-group-by-in-sql-server
and
http://www.sqlteam.com/article/how-to-use-group-by-with-distinct-aggregates-and-derived-tables

E 12°55'05.25"
N 56°04'39.16"

petenuffer
Starting Member

6 Posts

Posted - 2008-04-21 : 15:01:51

Got it now - thank you for your help!!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources