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
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