Applying GROUP BY when using multiple CASE WHEN

Question

select
 e_id
,CASE WHEN w_id in ('1','2') THEN w_id ELSE 0 END as new_w_id
,CASE WHEN w_id = 1 THEN fee END as value1
,CASE WHEN w_id = 2 THEN fee END as value2
,CASE WHEN w_id not in ('1','2') THEN fee END as value3
from table1

The above code gives me a result like:

e_id | new_w_id | value1 | value2 | value3
 1   |   1      |   100  | null   | null
 1   |   2      | null   |   150  | null
 1   |   0      | null   | null   |    50
 1   |   0      | null   | null   |   150

but I'd like to group by e_id and get result like:

e_id | new_w_id | value1 | value2 | value3
 1   |   1      |   100  | null   | null
 1   |   2      | null   |   150  | null
 1   |   0      | null   | null   |   200

I've tried

group by e_id

and

group by e_id, CASE WHEN w_id in ('1','2') THEN w_id ELSE 0 END

but neither of these are grouping my data as I'd like. Is there a solution to this?

Answer 1

I think you just need to aggregate your value columns

create table table1(e_id int, w_id int, fee int)


insert into table1
values(1,1,100),(1,2,150),(1,3,50),(1,4,150),
(2,1,100),(2,1,150),(2,2,50),(2,4,150)


select
 e_id
,CASE WHEN w_id in ('1','2') THEN w_id ELSE 0 END as new_w_id
,SUM(CASE WHEN w_id = 1 THEN fee END) as value1
,SUM(CASE WHEN w_id = 2 THEN fee END) as value2
,SUM(CASE WHEN w_id not in ('1','2') THEN fee END) as value3
from table1
group by e_id
,CASE WHEN w_id in ('1','2') THEN w_id ELSE 0 END 

Answer 2

@BobKlimes answer is correct but you can also do it with a CTE and might find it easier to read/work with particularly with large/complex queries. In my test they showed the same execution plan but YMMV.

WITH MyCTE AS (SELECT
                 e_id
                ,CASE WHEN w_id in ('1','2') THEN w_id ELSE 0 END as new_w_id
                ,CASE WHEN w_id = 1 THEN fee END as value1
                ,CASE WHEN w_id = 2 THEN fee END as value2
                ,CASE WHEN w_id not in ('1','2') THEN fee END as value3
            FROM table1
    ) 
SELECT e_id, new_w_id,
    SUM(value1) AS value1, SUM(value2) AS value2,
    SUM(value3) AS value3
FROM MyCTE
GROUP BY e_id, new_w_id