Write a query to display count of pass and fails as per subjects

 Expected Output:

 

 drop table t_PF;

create table t_PF ( subject varchar2(10), grade char(1) );

insert into t_PF values ('Maths','P');

insert into t_PF values ('Maths','F');

insert into t_PF values ('Maths','P');

insert into t_PF values ('Maths','P');

insert into t_PF values ('Science','F');

insert into t_PF values ('Science','P');

insert into t_PF values ('Science','F');

insert into t_PF values ('Science','F');

insert into t_PF values ('Social','F');

insert into t_PF values ('Social','F');

 

Solution:Using Case Statement

select

subject,

Count ( case grade when 'M' then 1 end) as Pass_Count,

Count ( case grade when 'F' then 1 end) as Fail_Count,

Count(*) as Total_Count

from t_PF

group by subject;

 

Solution:Using Pivot

with pf  as (SELECT * FROM

(

SELECT subject, grade

FROM t_PF

)

PIVOT

(

count(grade)

FOR grade IN ('M' as Pass_Count , 'F' as Fail_Count)

))

select subject, Pass_Count,Fail_Count,Pass_Count+Fail_Count as Total_Count from pf;

 

Solution:Using Decode

SELECT subject,SUM(DECODE(grade, 'P', 1,0)) AS Pass_Count,

SUM(DECODE(grade, 'F', 1,0)) AS Fail_Count,

count(*) AS Total_Count

FROM   t_PF

group by subject;