SQL statement to count occurrences in list

i would like to run an SQL query to calculate the number of passes and fails in a list. I've created a scenario with students and grades to better explain my problem.

I have 2 tables one being the student table

and another table being the grades

some rules To get a Car license you need to have passed Maths and English. To have a Boat license you need to have passed Maths English and Science.

How would I go about calculating the total number of students that can get a Car and Boat license?

To get an output like the following

I've added an SQLFiddle for further info.

http://sqlfiddle.com/#!18/900a7

Solution 1:

You can achieve this using a UNION to merge the count of the two licence types together

SELECT  'Car' AS LicenceType,
        COUNT(*) AS NumStudents
FROM    (
         SELECT   StudentId
         FROM     grades
         WHERE    (subject = 'Maths' AND status = 'pass') OR
                  (subject = 'English' AND status = 'pass')
         GROUP BY StudentId
         HAVING COUNT(*) = 2
       ) Car
  
UNION ALL

SELECT  'Boat' AS LicenceType,
        COUNT(*) AS NumStudents
FROM    (
         SELECT   StudentId
         FROM     grades
         WHERE    (subject = 'Maths' AND status = 'pass') OR
                  (subject = 'English' AND status = 'pass') OR
                  (subject = 'Science' AND status = 'pass')
         GROUP BY StudentId
         HAVING COUNT(*) = 3
       ) Boat

Incidentally, your SQL fiddle does not match the table of data in your question - student 3 has different pass / fail data