How to Sum by groups id and cat

June 9, 2023

I have a table

username     counts     cat
Jo            1         A
Jo            5         B
Jo            1         C
An            2         A
An            3         B
An            2         C

My goal to get sum by username and when the cat = A and B then sum so the output like this

username     catcount     
Jo            6
Jo            1
An            5
An            2

My query

select username, sum(case when cat ='A' and cat ='B' then counts Else counts end ) as catcount
FROM tableA
group by username, cat

Not sure why not sum and need support here. Thank you.

>Solution :

Use group by with a case expression that returns ‘B’ when the cat is ‘A’ (or ‘A’ when the cat is ‘B’), this will consider A and B as a single group.

select username,
       sum(counts) counts
from tableA
group by username, case when cat ='A' then 'B' else cat end
order by username

Or you may use the case as the following:

select username,
       case when cat in ('A', 'B') then 'AB' else cat end cat,
       sum(counts) counts
from tableA
group by username, case when cat in ('A', 'B') then 'AB' else cat end
order by username

demo