2.10 Summarizing Aggregated Classes

2.10.1 Problem

You want to calculate the number of times subsets fall into different classes, and you want to measure the sizes of those classes when classification is performed on already aggregated data. For example, say you want to count the number of course grades per student. A course grade is calculated by averaging the Score of all papers for a given course and then classifying that average according to Table 2-1. This is similar to the earlier recipe titled "Summarizing Classes of Sets," but this time we must aggregate the data twice.

2.10.2 Solution

The following SQL query combines elements of the previous two recipes to produce the desired result:

SELECT s.StudentName,(
   CASE WHEN s.Score>=22 THEN 'A' 
      WHEN s.Score>=19 THEN 'B'
      WHEN s.Score>=16 THEN 'C'
      WHEN s.Score>=13 THEN 'D'
      WHEN s.Score>=10 THEN 'E'
      ELSE 'F' END) Grade,
   COUNT(*) NoOfCourses
FROM (
   SELECT CourseId, StudentName, AVG(Score) AS Score 
   FROM Students 
   GROUP BY CourseId, StudentName) AS s
GROUP BY s.StudentName,
   CASE WHEN s.Score>=22 THEN 'A' 
      WHEN s.Score>=19 THEN 'B'
      WHEN s.Score>=16 THEN 'C'
      WHEN s.Score>=13 THEN 'D'
      WHEN s.Score>=10 THEN 'E'
      ELSE 'F' END
ORDER BY s.StudentName

The results from executing this query will look as follows:

StudentName Grade NoOfCourses 
----------- ----- ----------- 
Andrew      B     1
Andrew      E     1
Bert        E     1
Cindy       D     2

2.10.3 Discussion

At first glance, this query appears a bit complex and intimidating. To understand it, it's best to look at the query as a two-step process. The inline SELECT in the FROM clause calculates an average Score for each course and student combination. This average Score is computed from the individual Scores of all the term papers.

The results from the inline SELECT are fed into the outer query that translates the average Scores into letter grades and then counts up the number of times each grade occurs. The CASE statement in the SELECT list does the classification. The case statement in the GROUP BY clause aggregates the results by grade, allowing the count to be computed.