CODEWITHSUNDEEP
mysql
case1352
case1352

Reputation: 1136

sum column counts for two different values

i have a column that can hold 6 values (1,2,3,A,B,C)

i can count(*) them so it looks like this

select mycol, count(*) as ttl from mytable group by mycol;
    mycol  ttl
    1       46
    2       53
    3       10
    A       5 
    B       4
    C       2

but i want to sum the 1s and the As, and the 2s and the Bs like this

mycol total
var1    51
var2    57
var3    12

will a case statement work for this? like case 1 or A then treat them the same

Upvotes: 1

Views: 71

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269593

Yes, a case statement will work for this:

select (case when mycol in ('1', 'var1') then 'var1'
             when mycol in ('2', 'var2') then 'var2'
             when mycol in ('3', 'var3') then 'var3'
        end),
       sum(ttl) as Total
from mytable t
group by (case when mycol in ('1', 'var1') then 'var1'
               when mycol in ('2', 'var2') then 'var2'
               when mycol in ('3', 'var3') then 'var3'
          end);

EDIT:

If you just have the data with multiple rows for each value:

select (case when mycol in ('1', 'var1') then 'var1'
             when mycol in ('2', 'var2') then 'var2'
             when mycol in ('3', 'var3') then 'var3'
        end),
       count(*) as Total
from mytable t
group by (case when mycol in ('1', 'var1') then 'var1'
               when mycol in ('2', 'var2') then 'var2'
               when mycol in ('3', 'var3') then 'var3'
          end);

Upvotes: 3

Related Questions