March 24, 2008 at 4:47 pm
How would I select something like this:
select
code,
description,
sum of amount where priority=1,
sum of amount where priority=2,
sum of amount where priority=3
from table
group by code, description
Thanks so much!
March 24, 2008 at 5:13 pm
You're almost there:
select
code,
description,
sum( Case When priority=1 Then Amount Else 0 End ),
sum( Case When priority=2 Then Amount Else 0 End ),
sum( Case When priority=3 Then Amount Else 0 End ),
from table
group by code, description
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 24, 2008 at 6:01 pm
Depending on what your actually trying to accomplish, you might want to look into the new WINDOWED funstions like SUM() OVER (PARTITION BY ...)
i.e...
DECLARE @t TABLE (code INT, descr VARCHAR(10), priority INT, amount MONEY)
INSERT @t
SELECT 1, 'test1', 1, 10 UNION ALL
SELECT 1, 'test1', 1, 10 UNION ALL
SELECT 1, 'test1', 2, 10 UNION ALL
SELECT 1, 'test1', 2, 10 UNION ALL
SELECT 2, 'test1', 2, 10 UNION ALL
SELECT 2, 'test1', 3, 10 UNION ALL
SELECT 2, 'test1', 4, 10 UNION ALL
SELECT 2, 'test1', 4, 10 UNION ALL
SELECT 3, 'test1', 4, 10 UNION ALL
SELECT 3, 'test1', 5, 10 UNION ALL
SELECT 3, 'test1', 5, 10
SELECT DISTINCT
code, descr, priority
,SUM(Amount) OVER (PARTITION BY priority)
FROM
@t
giving a result of ...
code descr priority sumForCodePriority
----------- ---------- ----------- ---------------------
1 test1 1 20.00
1 test1 2 30.00
2 test1 2 30.00
2 test1 3 10.00
2 test1 4 30.00
3 test1 4 30.00
3 test1 5 20.00
AGAIN, this may not be what you want, but it is related, and it might help. 😀
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgViewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply