Getting two different types of sums with only one row-CodePudding

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?

CodePudding user response：

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

id	code2_total	overall
1	61	121
2	10	21

Note limit 1 is MySQL not SQL Server and doesn't help you here anyway.

Note also that providing the DDL DML as I have shown here makes it much easier for people to assist.