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.