SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table test (class int, branch varchar(10), status varchar(20))
insert into test values 
(1, 'Branch_A', 'INCLUDE'), 
(1, 'Branch_B', 'NOT INCLUDE'),
(1, 'Branch_C', 'NOT INCLUDE'),
(2, 'Branch_D', 'INCLUDE'),
(2, 'Branch_E', 'INCLUDE'),
(2, 'Branch_A', 'INCLUDE'),
(2, 'Branch_B', 'INCLUDE'),
(2, 'Branch_C', 'NOT INCLUDE')

select * from test


class
branch
status


1
Branch_A
INCLUDE

1
Branch_B
NOT INCLUDE

1
Branch_C
NOT INCLUDE

2
Branch_D
INCLUDE

2
Branch_E
INCLUDE

2
Branch_A
INCLUDE

2
Branch_B
INCLUDE

2
Branch_C
NOT INCLUDE


select t.class,
       count(case when t.status = 'INCLUDE' then 1 end) as INCLUDE,
       count(case when t.status = 'NOT INCLUDE' then 1 end) as [NOT INCLUDE]
from   test t
group by t.class


class
INCLUDE
NOT INCLUDE


1
1
2

2
4
1


Warning: Null value is eliminated by an aggregate or other SET operation.


select t.class,
       sum(case when t.status = 'INCLUDE' then 1 else 0 end) as INCLUDE,
       sum(case when t.status = 'NOT INCLUDE' then 1 else 0 end) as [NOT INCLUDE]
from   test t
group by t.class


class
INCLUDE
NOT INCLUDE


1
1
2

2
4
1

class	branch	status
1	Branch_A	INCLUDE
1	Branch_B	NOT INCLUDE
1	Branch_C	NOT INCLUDE
2	Branch_D	INCLUDE
2	Branch_E	INCLUDE
2	Branch_A	INCLUDE
2	Branch_B	INCLUDE
2	Branch_C	NOT INCLUDE