Multiple counts in single query with multiple joining of tables - SQL

Question

I have to join three tables shown below into a single table.

Table: Subjects

Subject_id   Subject_Name
--------------------------
 1             English
 2             Arabic
 3             Hindi

Table : Students

Student_id     Student_Name    Subject_id
------------------------------------------
XXX               Veena            1
YYY                Ram             1
AAA                Raiza           2
DDD                David           3 
BBB                Geeth           3

Table : StudentVsSubject

Subject_id   Student_id      Status (1  =passed, 0 = failed)
-------------------------------------------------------------
  1             XXX             1
  2             AAA             0
  3             BBB             1
  3             DDD             0

And the output should be :

Subject TotalStudents  Passed   Failed  NotAttended
---------------------------------------------------
English    2            1          0          1
Arabic     1            0          1          0
Hindi      2            1          1          0

I'm stuck with by getting TotalStudents only.

SELECT Subjects.Subject_name
    ,count(Students.Students) AS TotalStudents
FROM Subjects
LEFT JOIN Students ON Subjects.Subject_id = Students.Subject_id
GROUP BY Subjects.Subject_name

Use case expressions to do conditional aggregation!

jarlh
– jarlh

2020-02-07 10:47:00 +00:00
Commented Feb 7, 2020 at 10:47 — jarlh
– jarlh, Commented Feb 7, 2020 at 10:47

Nick · Accepted Answer · 2020-02-07 10:59:45Z

This query will give you the results you want. Note that I had to fix an inconsistency in your data, the second entry in StudentVsSubject should be (2, 'AAA', 0), not (2, 'YYY', 0) as Student AAA took Arabic, not Student YYY. To get the Passed and Failed counts we simply SUM either Status or 1-Status; to get the NotAttended count we SUM the NULL values from the LEFT JOIN to StudentVsSubject.

SELECT sb.Subject_Name, 
       COUNT(st.Student_id) AS TotalStudents,
       SUM(COALESCE(ss.Status, 0)) AS Passed,
       SUM(COALESCE(1 - ss.Status, 0)) AS Failed,
       SUM(CASE WHEN ss.Status IS NULL THEN 1 ELSE 0 END) AS NotAttended
FROM Subjects sb
JOIN Students st ON st.Subject_id = sb.Subject_id
LEFT JOIN StudentVsSubject ss ON ss.Subject_id = sb.Subject_id AND ss.Student_id = st.Student_id
GROUP BY sb.Subject_id, sb.Subject_Name
ORDER BY sb.Subject_id

Output:

Subject_Name    TotalStudents   Passed  Failed  NotAttended
English         2               1       0       1
Arabic          1               0       1       0
Hindi           2               1       1       0

Demo on SQLFiddle

Martin · Accepted Answer · 2020-02-07 10:47:12Z

You could use an aggregated subquery to achieve this:

SELECT  sub.Subject_name,
        a.TotalStudents,
        a.Passed,
        a.Failed
  FROM  Subjects sub
    LEFT JOIN   (
                SELECT  Subject_id,
                        COUNT(Student_id) AS TotalStudents,
                        SUM(Status) AS Passed,
                        SUM(IIF(Status = 0, 1, 0)) AS Failed
                  FROM  StudentVsSubject
                  GROUP BY Subject_id
                ) a ON a.Subject_id = sub.Subject_id

Output (based on your above dataset):

Subject_name  TotalStudents  Passed  Failed
English       1              1       0
Arabic        1              0       1
Hindi         2              1       1

Collectives™ on Stack Overflow

Multiple counts in single query with multiple joining of tables - SQL

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related