Section 2.8. Summarizing Classes of Sets

   

2.8 Summarizing Classes of Sets

2.8.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 nonaggregated data. As an example of this type of problem, let's say you want to count the number of A papers, B papers, and so forth for each student.

2.8.2 Solution

An alternative way of stating the problem is to say that you need to count the number of times each student is given each grade. The following query does this:

 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(*) NoOfPapers FROM Students 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 returned by this query will resemble these:

 StudentName Grade NoOfPapers   ----------- ----- -----------  Andrew      A     1 Andrew      B     2 Andrew      D     1 Andrew      E     2 Bert        D     2 Bert        E     1 Cindy       C     2 Cindy       D     1 Cindy       E     2 

2.8.3 Discussion

The code demonstrates an interesting feature of SQL that is not used very often ” the use of a CASE statement within a GROUP BY clause. This not often seen, though it is a fairly powerful construct. The first CASE statement, the one in the main part of the query, assigns a grade to each term paper. Instead of aggregating the results by student and paper, the results are then aggregated by student and grade. By counting the number of records in each group, we find out how many A grades a student has, how many B grades, and so forth. The following example illustrates this grouping and counting process:

 StudentName Grade NoOfPapers   ----------- ----- -----------  Andrew      B Andrew      B COUNT(*)          2 Bert        D   Bert        D COUNT(*)          2 

If you wanted to, you could extend the query so that it also calculated the percentage that each grade represented out of the total number of term papers taken. The following SELECT list shows the extra column that you would need to add to do this:

 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(*) NoOfPapers,  100*count(*)/(    SELECT count(*) FROM Students s1       WHERE s1.StudentName=s.StudentName) Per  ... 

This modified query will return the following results:

 StudentName Grade NoOfPapers  Per          ----------- ----- ----------- -----------  Andrew      A     1           16 Andrew      B     2           33 Andrew      D     1           16 Andrew      E     2           33 Bert        D     2           66 Bert        E     1           33 Cindy       C     2           40 Cindy       D     1           20 Cindy       E     2           40 
   


Transact-SQL Cookbook
Transact-SQL Cookbook
ISBN: 1565927567
EAN: 2147483647
Year: 2005
Pages: 152

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net