CODEWITHSUNDEEP
sql
bubblebop
bubblebop

Reputation: 37

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

Upvotes: 0

Views: 74

Answers (3)

Stu
Stu

Reputation: 32609

You can use window functions to count qualifying students in a CTE and then aggregate the results:

with t as (
    select studentId, 
        Sum(case when subject in ('maths','english') then 1 end) over(partition by studentid) Car,
        Sum(case when subject in ('maths','english', 'science') then 1 end) over(partition by studentid) Boat
    from grades
    where status='pass'
)
select distinct 'Number of Car Licenses', Sum(Count(distinct car)) over() 
from t
where car=2
group by studentid
union all
select distinct 'Number of Boat Licenses', Sum(Count(distinct Boat)) over() 
from t
where boat=3
group by studentid

Upvotes: 0

Stephan
Stephan

Reputation: 6018

Need to use a COUNT to track requirements met, and see if each student met all the requirements. Then aggregate the results into your desired result set (your desired result set values do not seem to match your sample data)

SELECT A.StudentID
        ,CarLicenseRequirementsMet  = CASE WHEN COUNT(CASE WHEN B.[Subject] IN ('MATHS','ENGLISH') THEN 1 END) = 2/*Both requirements met*/ THEN 'Y' END
        ,BoatLicenseRequirementsMet = CASE WHEN COUNT(CASE WHEN B.[Subject] IN ('MATHS','ENGLISH','SCIENCE') THEN 1 END) = 3 /*All 3 requirements met*/ THEN 'Y' END
INTO #StudentRequirement
FROM student AS A
INNER JOIN grades AS B
    ON A.StudentID = B.StudentID
WHERE B.[Status] = 'Pass'
GROUP BY A.StudentID

/*Returned for reference only*/
SELECT * FROM #StudentRequirement

/*Aggregated results*/
SELECT 'Number of Car Licenses',COUNT(*) FROM #StudentRequirement WHERE CarLicenseRequirementsMet = 'Y'
UNION ALL
SELECT 'Number of Boat Licenses',COUNT(*) FROM #StudentRequirement WHERE BoatLicenseRequirementsMet = 'Y'

Upvotes: 0

SE1986
SE1986

Reputation: 2750

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

Upvotes: 1

Related Questions