CODEWITHSUNDEEP
sqldatabasepostgresql
Jet Waitforit Torres
Jet Waitforit Torres

Reputation: 161

SQL Query to find total number of students enrolled in a subject

Class Table has class_id and class_name columns with the ff values:

class_id | class_Name
1        | Algebra
2        | History
3        | PE

Student table has student_id and student_name column:

student_id | student_name
1          | Kylo Ren
2          | Rey

The third table which is a junction table for both table has the following columns class_id and student_id

class_id | student_id
1        | 1
1        | 2
2        | 1
3        | 2

How can I find the number of students enrolled per subjects?

Output should be like this

Class Name           | Number of Students
Algebra              | 2
History              | 1
PE                   | 1

Then after that edit the query so that it would only show the class name and number of students greater than 2

Thanks!

Upvotes: 1

Views: 17489

Answers (2)

lmarqs
lmarqs

Reputation: 1471

Maybe I got this wrong...

SELECT 
    c.class_name "Class Name", 
    count(distinct j.student_id) "Number of Students"
FROM
    class c
    LEFT JOIN junction j ON j.class_id = c.class_id
GROUP BY
    1

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521194

Aggregate the junction table by student, and then assert that a matching student has all courses:

WITH cte AS (
    SELECT student_id
    FROM junction
    GROUP BY student_id
    HAVING COUNT(class_id) = (SELECT COUNT(*) FROM class)
)

SELECF COUNT(*) AS num_all_classes FROM cte;

Upvotes: 2

Related Questions