Oracle SQL to calculate percentage

I’m trying to calculate the percentage based on the total students for each subject and by grade distribution.

I have table with the following fields:

Student Id
Term,
Course
final grade

Sample Data

I wrote the below query to get the total per course and per grade

select distinct  Count( StudentID ) as "Total",
       COURSE,FINAL_GRADE,subject,from table
 where term in('2000200')
       and subject in ('ENG111','ENG112,'ENG121',,,,,,,,,,,,etc)
       and FINAL_GRADE IN('A','B','C','F')
 group by 1

​x
 
select distinct  Count( StudentID ) as "Total",       COURSE,FINAL_GRADE,subject,from table where term in('2000200')       and subject in ('ENG111','ENG112,'ENG121',,,,,,,,,,,,etc)       and FINAL_GRADE IN('A','B','C','F') group by 1​

I cannot use the below code because it will calculate the overall total and not per subject :

count(ID) * 100.0 / (select count(*) from Table) as 'Percentage'

 
count(ID) * 100.0 / (select count(*) from Table) as 'Percentage'​

the output should be similar to the below

Output required

please help

thank you

Answer

If I understand correctly, you want aggregation with a window function to calculate the total for the subject:

select subject, grade, count(*) as total,
     100.0 * count(*) / sum(count(*)) over (partition by subject)
from t
group by subject, grade

 
select subject, grade, count(*) as total,     100.0 * count(*) / sum(count(*)) over (partition by subject)from tgroup by subject, grade​

SqlFiddle

Advertisement

Answer