add batch remove batch split batch comment selection show hidden batches hide batch highlight batch
db<>fiddle
donate feedback about
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE t
([id] int, [name] varchar(1), [status] int)
;
INSERT INTO t
([id], [name], [status])
VALUES
(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)
;

9 rows affected
select id
,count(*) as 'total count'
,count(case status when 3 then 1 end) as 'count(status3)'
,count(case status when 2 then 1 end) as 'count(status2)'
,count(case status when 1 then 1 end) as 'count(status1)'
from t
group by id
id total count count(status3) count(status2) count(status1)
2 5 1 2 2
3 4 0 2 2
Warning: Null value is eliminated by an aggregate or other SET operation.