Table counts from same table

Question

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;