sum values with sql server

Question

I have the following table test

id    Actual     Budget     CA      RUB_ID 
-------------------------------------------
1      20         30        201     902
1      2         330        202     902
1      220       130        207     90
1      21         30        20      12

How can get the following result

Actual     Budget     CA     
20        130         12

I need to do sum Actual if RUB_ID =902 , sum Budget if RUB_ID =90 ,sum CA if RUB_ID =12

select 
    id, 
    case 
       when RUB_ID = 902 then sum(Actual) AS Actual 
       else case 
              when RUB_ID = 90 
                then sum(Budget) as Budget 
                else case 
                        when RUB_ID = 12 then sum(CA) as CA 
FROM 
    TEST 
group by 
    id

The query does not return what I am looking for , how can I modify it ?

select id, sum(case when rub_id=902 then actual else 0 end) as sum_actual, sum(case when rub_id=90 then budget else 0 end) as sum_budget from test group by id — gloomy.penguin
– gloomy.penguin, Commented Oct 2, 2014 at 15:53
@gloomy.penguin post that as an answer.. not in the comments. — crthompson
– crthompson, Commented Oct 2, 2014 at 15:55
@paqogomez - it's not really an answer... just a suggestion. I wasn't entirely sure on what they wanted so I just made it a comment... I don't like having to edit my answers a bunch if I post too soon. — gloomy.penguin
– gloomy.penguin, Commented Oct 2, 2014 at 15:58

FuzzyTree · Accepted Answer · 2014-10-02 15:54:53Z

3

Put your case statements inside the sum functions

select
    sum(case when RUB_ID = 902 then Actual else 0 end) Actual,
    sum(case when RUB_ID = 90 then Budget else 0 end) Budget,
    sum(case when RUB_ID = 12 then CA else 0 end) CA
from test where RUB_ID IN (902,90,12)

if you want these results by id

select
    id,
    sum(case when RUB_ID = 902 then Actual else 0 end) Actual,
    sum(case when RUB_ID = 90 then Budget else 0 end) Budget,
    sum(case when RUB_ID = 12 then CA else 0 end) CA
from test where RUB_ID IN (902,90,12)
group by id

answered Oct 2, 2014 at 15:54

FuzzyTree

32.4k3 gold badges58 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

gloomy.penguin Over a year ago

i think you might need to make then actual to... then actual, then budget, then ca

crthompson Over a year ago

@gloomy.penguin I think you'll find that he has it right on. What is missing from that?

gloomy.penguin Over a year ago

@paqogomez - oh, sorry. this answer has been updated a few times (within the 5 min window that doesn't show edits), originally, all the case statements ended with then budget else 0). a few things have been modified since then. my comment was made for the original post.

Ryan B. · Accepted Answer · 2014-10-02 15:58:16Z

2

Something like this:

SELECT
    id
    , SUM(CASE RUB_ID WHEN 902 THEN Actual ELSE 0 END) as Actual
    , SUM(CASE RUB_ID WHEN 90 THEN Budget ELSE 0 END) as Budget
    , SUM(CASE RUB_ID WHEN 12 THEN CA ELSE 0 END) as CA
FROM TEST
GROUP BY id

answered Oct 2, 2014 at 15:58

Ryan B.

3,6852 gold badges22 silver badges28 bronze badges

Collectives™ on Stack Overflow

sum values with sql server

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related