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

studentid	firstname	lastname
1	Sponge	Bob
2	Patrick	Star

and another table being the grades

studentid	subject	score	status
1	Maths	70	PASS
1	English	70	PASS
1	Science	60	FAIL
2	Maths	75	PASS
2	English	80	PASS
2	Science	75	PASS
3	Maths	70	PASS
3	English	80	PASS
3	Science	75	PASS

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

Number of Car licenses	3
NBumber of Boat licenses	2

I’ve added an SQLFiddle for further info.

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

Answer

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