Grouping with case statement? - 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 2005 Forums

Transact-SQL (2005)

Grouping with case statement?

Author

Topic

pmartin
Starting Member

2 Posts

Posted - 2008-04-02 : 10:48:00

I am attempting to get one line with all the price info for three fields.
The end result looks like this
PT_UD_Key PatternPrice CastingPrice MachiningPrice
FG-020006-000 0.00 1840.00 0.00
FG-020006-000 750.00 0.00 0.00
FG-020006-000 0.00 0.00 3945.00

I would like it to look like this

PT_UD_Key PatternPrice CastingPrice MachiningPrice
FG-020006-000 750.00 1840.00 3945.00

What am I doing wrong?
Here is my query

SELECT PT_UD_KEY,
'PatternPrice' =
CASE
WHEN PT_UD_NUMBER = 1 THEN TOTAL
ELSE 0.00
END,
'CastingPrice' =
CASE
WHEN PT_UD_NUMBER = 15 THEN TOTAL
ELSE 0.00
END,
'MachiningPrice' =
CASE
WHEN PT_UD_NUMBER = 3 THEN TOTAL
ELSE 0.00
END
FROM CAL.dbo.EXT00103
WHERE PT_WINDOW_ID='ITEM PRICE' AND LEFT(PT_UD_KEY,2)='FG' AND PT_UD_NUMBER IN (1,3,15) AND TOTAL >0.00
ORDER BY PT_UD_KEY

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-04-02 : 11:00:35

Try this:

SELECT PT_UD_KEY, 
'PatternPrice' = 
sum(CASE 
WHEN PT_UD_NUMBER = 1 THEN TOTAL
ELSE 0.00
END),
'CastingPrice' = 
sum(CASE 
WHEN PT_UD_NUMBER = 15 THEN TOTAL
ELSE 0.00
END),
'MachiningPrice' = 
sum(CASE 
WHEN PT_UD_NUMBER = 3 THEN TOTAL
ELSE 0.00
END)
FROM CAL.dbo.EXT00103
WHERE PT_WINDOW_ID='ITEM PRICE' AND LEFT(PT_UD_KEY,2)='FG' AND PT_UD_NUMBER IN (1,3,15) AND TOTAL >0.00
group by PT_UD_KEY
ORDER BY PT_UD_KEY

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

pmartin
Starting Member

2 Posts

Posted - 2008-04-02 : 16:45:31

That worked perfectly. I couldnt get the Sum function to work properly and I gave up. Thank you very much.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-04-03 : 02:15:48

Also avoid having single quotes around alias names

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources