I have a table that looks something like this:

id name status
2  a     1
2  a     2
2  a     3
2  a     2
2  a     1
3  b     2
3  b     1
3  b     2
3  b     1

and the resultant i want is:

id  name   total count    count(status3)   count(status2)   count(status1)
2   a        5                  1              2              2
3   b        4                  0              2              2

please help me get this result somehow, i can just get id, name or one of them at a time, don't know how to put a clause to get this table at once.

CodePudding user response：

Here's a simple solution using group by and case when.

select id
      ,count(*) as 'total count'
      ,count(case status when 3 then 1 end) as 'count(status1)'
      ,count(case status when 2 then 1 end) as 'count(status3)'
      ,count(case status when 1 then 1 end) as 'count(status2)'
from t 
group by id

Fiddle