Section 2.13. Reporting the Size of a Set s Complement

2.13 Reporting the Size of a Set's Complement

2.13.1 Problem

You want to report the number of missing values for a set. As an example, assume that each student must submit four term papers for each course. Not all students have submitted all required papers, and you want to generate a report showing the number that each student has yet to submit for each course.

2.13.2 Solution

 SELECT  s.StudentName, s.CourseId, 4-COUNT(TermPaper) Missing FROM Students s GROUP BY s.StudentName, s.CourseId ORDER BY s.StudentName 

Using the sample data provided for this chapter, the query in this solution should return the following result:

 StudentName  CourseId  Missing      ------------ --------- -----------  Andrew       ACCN101   1 Andrew       MGMT120   1 Bert         ACCN101   1 Cindy        ACCN101   2 Cindy        MGMT120   1 

2.13.3 Discussion

This query is very straightforward. It's an aggregate query that summarizes results by course and student combination. The aggregate function COUNT(TermPaper) is used to count the number of papers a student submits for a course. That result is then subtracted from the required number ” four in this case ” and the result is the number of missing term papers.