CODEWITHSUNDEEP
sqlmysql
user253530
user253530

Reputation: 2591

Sql query to get total number of students in a class

I have this SQL query:

SELECT c.id as ID, c.class_name as CLASS, COUNT(e.student_id) AS STUDENT_COUNT 
FROM classes as c
LEFT JOIN enrollments as e on e.class_id = c.id
where c.teacher_id = 8 AND e.approved = 1
group by c.class_name;

What I try to do is to get all the classes and the number of students that have been approved in those classes. The sql query should return something like

ID            CLASS          STUDENT_COUNT
1             Math              0
2             Biology           2
3             Algebra           1
4             Literature        5

The problem is that because of e.approved = 1 i don't get any classes at all. Suggestions?

My schema is as follows

CLASSES table

id, teacher_id, class_name, grade

ENROLLMENTS table

id, class_id, student_id, approved

Upvotes: 2

Views: 11605

Answers (1)

Chandu
Chandu

Reputation: 82903

Change your query to use the e.approved in the left join condition:

SELECT c.id as ID, c.class_name as CLASS, COUNT(e.student_id) AS STUDENT_COUNT 
FROM classes as c
LEFT JOIN enrollments as e on e.class_id = c.id 
AND  e.approved = 1
where c.teacher_id = 8 
group by c.class_name;

Upvotes: 4

Related Questions