SQL Query to get multiple resultant on single column

Question

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.

Answer 1

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

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

Fiddle

Answer 2

Here's a way to solve it using pivot.

select *
from  (select status,id, count(*) over (partition by id) as "total count" from t) tmp
       pivot (count(status) for status in ([1],[2],[3])) pvt

d	total count	1	2	3
3	4	2	2	0
2	5	2	2	1

Fiddle