Hi, I have a requirement as follows. sno id Role is_active is_deleted 1 100 SM y n 2 101 SM n y 3 102 SM y n 4 103 BC n y 5 104 BC y n 6 105 BC y n 7 103 AI n y 8 104 AI y n 9 105 AI y n Output: Role Total Active Deleted SM 3 2 1 BC 3 2 1 AI 3 2 1 Table structure as follows. CREATE TABLE sample ( id int,Role CHAR(2), is_active CHAR(1), is_deleted CHAR(1) ); insert into sample values(100,'SM','y','n'),(101,'SM','n','y'), (102,'SM','y','n'),(103,'BC','n','y'),(104,'BC','y','n'),(105,'BC','y','n'), (103,'AI','n','y'),(104,'AI','y','n'),(105,'AI','y','n'); select * from sample;

Hello, select Role, count (Role) as Total, sum ( case when is_active= 'y' then 1 else 0 end ) as Active, sum ( case when is_deleted= 'y' then 1 else 0 end ) as Deleted from sample group by Role

try This for complete solution declare @sample TABLE (id int,Role CHAR(2),is_active CHAR(1),is_deleted CHAR(1)); insert into @sample values (100,'SM','y','n'), (101,'SM','n','y'), (102,'SM','y','n'), (103,'BC','n','y'), (104,'BC','y','n'), (105,'BC','y','n'), (103,'AI','n','y'), (104,'AI','y','n'), (105,'AI','y','n'); select Role, sum(case when is_active='y' then 1 when is_deleted='y' then 1 else 0 end) as Total, sum(case when is_active='y' then 1 else 0 end) as Active, sum(case when is_deleted='y' then 1 else 0 end) as Deleted from @sample group by Role