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

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.