Table counts from same table

Sai Reddy

7y871

Hi,

I have a requirement as follows.

Output:

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;

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Piyush Pansuriya7y ago
Accepted answer
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
Jayesh Vaghasiya7y ago
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