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

You need a StudentName condition in the sub-query as well. Make it a correlated sub-query. — jarlh, Oct 30 '17 at 15:44

Matthew McPeak · Answer 1 · 2017-10-30T17:11:56.527

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  ║
╚══════════════════════════════════════════════════════════════════╝

Thanks Methew, although database is intersystem cache with simple SQL but your answer gives a good direction — Joshua Spens, Oct 30 '17 at 23:20

score 1 · Accepted Answer · answered Oct 30 '17 at 16:09

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.

score 0 · Answer 3 · answered Oct 30 '17 at 17:40

Here’s a more or less conventional way using SQL Server 2008 and up. (There may be more expedient ways to write this using the statistical windowing functions in later versions.)

This will return data for all students in at least one class, for all classes with at least one student. If the tables are large, uncomment the where clause to get data for one student a a time

First, the testing data:

CREATE TABLE #Test
 (
   ClassSession  varchar(20)  not null
  ,StudentName   varchar(20)  not null
 )

INSERT #Test values 
  ('Evening', 'Ben')
 ,('Morning', 'Chris')
 ,('Afternoon', 'Roger')
 ,('Evening', 'Ben')
 ,('Afternoon', 'Ben')
 ,('Morning', 'Roger')
 ,('Morning', 'Ben')
 ,('Afternoon', 'Chris')


SELECT *
 from #Test

And the query:

WITH cteClasses
 as (--  First, get the list of classes
     SELECT distinct ClassSession
      from #Test
     )
 ,cteStudents
  as (--  Next, get a list of all students
      SELECT
         StudentName
        ,count(*) * 1.00  ClassCount
       from #Test
       --where StudenName = @StudentParameter
       group by StudentName
     )
 --  Mush them all together, and...
 SELECT
   st.StudentName
  ,cl.ClassSession
  ,count(te.StudentName) / st.ClassCount * 100  Percentage
 from cteStudents st
  cross join cteClasses cl
  left join #Test te
   on te.ClassSession = cl.ClassSession
    and te.StudentName = st.StudentName
 group by
   st.StudentName
  ,cl.ClassSession
  ,st.ClassCount
 order by 
   st.StudentName
  ,cl.ClassSession

Thanks Philip, the database is intersystem cache but i like your approach — Joshua Spens, Oct 31 '17 at 23:14

SQL Percentage calculated dynamically

3 Answers3