Getting two different types of sums with only one row

March 19, 2022

I have a table that looks like this:

id  code  total
1   2     30
1   4     60
1   2     31
2   2     10
2   4     11

What I’d like to do, is basically get one row per id for the sum of records for code 2 and the sum of records for all codes for that id. So something like this:

id  code2_total  overall
1   61           121
2   10           21

I’ve tried the following:

select id
    , abs(sum(total) over (partition by id)) as overall
    , (select sum(total) from table where code = '2' group by id) as code2_total
   from table limit 1

But I’m getting multiple items in the subquery error. How can I achieve something like this?

>Solution :

Use group by with a regular sum and a conditional sum (i.e. using a case expression).

declare @MyTable table (id int, code int, total int);

insert into @MyTable (id, code, total)
    values
    (1, 2, 30),
    (1, 4, 60),
    (1, 2, 31),
    (2, 2, 10),
    (2, 4, 11);

select id
    , sum(case when code = 2 then total else 0 end) code2_total
    , sum(total) overall
from @MyTable
group by id
order by id;

Returns