Help with Group By and Select Sum()

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

Help with Group By and Select Sum()

Author

Topic

DaveBF
Yak Posting Veteran

89 Posts

Posted - 2010-10-22 : 14:51:52

How do I modify the following statement so that for each unique Substring(c.orgcode,1,2) it will show the sum of all the a.amounts? I tried several different for the Group clause but they either gave an error or the wrong totals. I think I need Sum(a.amount) in the first line, but I'm not sure what goes in the Group by clause. Thanks.

select SUBSTRING(c.OrgCode,1,2), a.amount
from Awards a
inner join Person p on p.Person_ID = a.AWA_Person_ID
INNER join Position r on r.Pos_Person_ID = p.Person_ID
inner join LU_CostCenters c on c.LU_CostCenters_ID = r.Pos_LU_CostCenters_ID
inner join AwardOrganizations o on o.Prefix = SUBSTRING(c.OrgCode,1,2)

Sachin.Nand

2937 Posts

Posted - 2010-10-22 : 14:57:37

select SUBSTRING(c.OrgCode,1,2), sum(a.amount)
from Awards a
inner join Person p on p.Person_ID = a.AWA_Person_ID
INNER join Position r on r.Pos_Person_ID = p.Person_ID
inner join LU_CostCenters c on c.LU_CostCenters_ID = r.Pos_LU_CostCenters_ID
inner join AwardOrganizations o on o.Prefix = SUBSTRING(c.OrgCode,1,2)
group by SUBSTRING(c.OrgCode,1,2)

PBUH

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-10-22 : 14:58:27

Anything you're not summing,counting, averaging, etc. in your serlect list goes in the group by as well

select o.Prefix as OrgCode, SUM(a.amount) as Total
from Awards a
inner join Person p on p.Person_ID = a.AWA_Person_ID
INNER join Position r on r.Pos_Person_ID = p.Person_ID
inner join LU_CostCenters c on c.LU_CostCenters_ID = r.Pos_LU_CostCenters_ID
inner join AwardOrganizations o on o.Prefix = SUBSTRING(c.OrgCode,1,2)

GROUP BY o.Prefix

Everyday I learn something that somebody else already knew

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources