Sum Columns Based on Column Values

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 2008 Forums

Transact-SQL (2008)

Sum Columns Based on Column Values

Author

Topic

MsLady
Starting Member

5 Posts

Posted - 2009-12-30 : 11:10:24

I am working on getting summed output based on differing values within one column. I have the following:
SELECT dbo.CPRRC.Description, dbo.CPRRB.Code, dbo.CPRRD.Form,
SUM(dbo.CPRRD.Cost) AS Cost
FROM dbo.CPRRCINNER JOIN
dbo.CPRRD ON dbo.CPRRC.Code = dbo.CPRRD.Cat INNER JOIN
dbo.CPRRB ON dbo.CPRRD.BIFK = dbo.CPRRB.BCode
GROUP BY dbo.CPRRB.Code, dbo.CPRRD.Form, dbo.CPRRC.Description
HAVING (dbo.CPRRD.Form = 'A') OR
(dbo.CPRRD.Form = 'B')
ORDER BY dbo.CPRRBm.Code

Current Output
Description Code Form Cost
Building 1234L A 100
Building 1234R B 200
Computer 2587L A 1000
Computer 2587R B 1500
Transportation 6854L A 3750
Transportation 6854R B 7500
Furniture 1973L A 10250
Furniture 1973R B 27500
Truck 1000L A 45000

Desired Output
Description Cost
Building 300
Computer 2500
Transportation 11250
Furniture 37750
Truck 45000

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-12-30 : 11:16:05

This?

SELECT dbo.CPRRC.Description
,SUM(dbo.CPRRD.Cost) AS Cost
FROM dbo.CPRRCINNER JOIN
dbo.CPRRD ON dbo.CPRRC.Code = dbo.CPRRD.Cat
where dbo.CPRRD.Form  in('A','B')
GROUP BY dbo.CPRRC.Description

brian147
Starting Member

10 Posts

Posted - 2009-12-30 : 11:20:45

just delete the unwanted columns from the SELECT list and the GROUPBY list
eg:
SELECT dbo.CPRRC.Description, SUM(dbo.CPRRD.Cost) AS Cost
etc
GROUP BY dbo.CPRRC.Description
etc

brian147
Starting Member

10 Posts

Posted - 2009-12-30 : 11:23:43

i was a bit slow there, the "aged yak" got in just before me.

MsLady
Starting Member

5 Posts

Posted - 2009-12-30 : 11:46:57

Vijayisonly,

Thank you very much.
It worked as expected.

MsLady.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-12-30 : 12:42:45

You're welcome.