SQL Percentage calculated dynamically

Question

How to calculate percentage dynamically in SQL?

Let's say you have a following table called Classes:

ClassSession       StudentName
---------------------------------
Evening            Ben
Morning            Chris
Afternoon          Roger
Evening            Ben
Afternoon          Ben
Morning            Roger
Morning            Ben
Afternoon          Chris

Let's say for Ben, I am expecting

Evening = 50 %
Afternoon = 25%
Morning = 25%

for Chris I am expecting

Morning = 50%
Afternoon = 50%
Evening = 0 %

so ClassSession (three sessions) should be constant for comparison

So far I have tried the following SQL statements:

Select 
    ClassSession,
    (Count(ClassSession) * 100 / (Select Count(*) From Classes)) as Percentage
From 
    Classes
Where 
    StudentName = 'Chris'
Group By 
    ClassSession

Answer 1

The hard part is getting the zeros to show up for students that do not have any classes in a given session.

This is a job for a PARTITION outer join.

select c.studentname, 
       s.classsession, 
       round(ratio_to_report(count(c.classsession)) 
            over ( partition by c.studentname),2) pct
from c partition by ( studentname ) 
   right outer join ( SELECT distinct classsession from c ) s 
          on s.classsession = c.classsession
group by c.studentname, s.classsession
order by c.studentname, s.classsession;

Note the PARTITION keyword in the join. This tells Oracle to perform the outer join for each partition. So, if a given studentname does not have a classsession, add it for that student.

Also, ratio_to_report is a good function for calculating percentages.

Here is a full example, with data:

with c (ClassSession, StudentName) AS ( 
SELECT 'Evening',            'Ben' FROM DUAL UNION ALL
SELECT 'Morning',            'Chris' FROM DUAL UNION ALL
SELECT 'Afternoon',          'Roger' FROM DUAL UNION ALL
SELECT 'Evening',            'Ben' FROM DUAL UNION ALL
SELECT 'Afternoon',          'Ben' FROM DUAL UNION ALL
SELECT 'Morning',            'Roger' FROM DUAL UNION ALL
SELECT 'Morning',            'Ben' FROM DUAL UNION ALL
SELECT 'Afternoon',          'Chris' FROM DUAL)
select c.studentname, 
       s.classsession, 
       round(ratio_to_report(count(c.classsession)) 
            over ( partition by c.studentname),2) pct
from c partition by ( studentname ) 
   right outer join ( SELECT distinct classsession from c ) s on s.classsession = c.classsession
group by c.studentname, s.classsession
order by c.studentname, s.classsession;


╔══════════════════════════════════════════════════════════════════╗
║ STUDENTNAME CLASSSESSION PCT                                     ║
╠══════════════════════════════════════════════════════════════════╣
║ ----------- ------------ --------------------------------------  ║
║ Ben         Afternoon                                      0.25  ║
║ Ben         Evening                                         0.5  ║
║ Ben         Morning                                        0.25  ║
║ Chris       Afternoon                                       0.5  ║
║ Chris       Evening                                           0  ║
║ Chris       Morning                                         0.5  ║
║ Roger       Afternoon                                       0.5  ║
║ Roger       Evening                                           0  ║
║ Roger       Morning                                         0.5  ║
╚══════════════════════════════════════════════════════════════════╝

Answer 2

One method uses conditional aggregation and window functions:

Select ClassSession,
       (sum(case when StudentName = 'Chris' then 100.0 else 0 end) /
        sum(sum(case when StudentName = 'Chris' then 100.0 else 0 end)) over ()
       ) as Percentage
From Classes
Group By ClassSession;

This will ensure that event the zeros show up.