a little more than 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 2000 Forums

SQL Server Development (2000)

a little more than SUM()

Author

Topic

kashyap81
Starting Member

9 Posts

Posted - 2008-06-07 : 02:09:07

so lets say I have a table

TAD_ITEM_DETAILS ->

ItemID ComponentType Price
------------------------------
1 1 100
1 1 200
1 2 50
2 1 100
3 2 100
.
.
.

I want a select statement that does something like this:

select
itemid,
sum(price) as TotalItemPrice,
sum(price of componenttype=1) as TotalPriceComponentType1
from
TAD_ITEM_DETAILS
group by
itemid

Output:
ItemID TotalItemPrice TotalPriceComponentType1
1 350 300
2 100 100
3 100 0

The problem is... I cant find a good way to do sum(price of componenttype=1)

any ideas?

DiaTree.com
D.I.P. Pvt Ltd

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-07 : 03:20:53

select
itemid,
sum(price) as TotalItemPrice,
sum(case when componenttype=1 then price else 0 end) as TotalPriceComponentType1
from
TAD_ITEM_DETAILS
group by
itemid

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

kashyap81
Starting Member

9 Posts

Posted - 2008-06-07 : 06:36:32

That worked.

Thank you.

DiaTree.com
D.I.P. Pvt Ltd

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources