Write a query to count the students of each course, including empty courses

Description: Show every course with the number of its students. A course without students must show 0.

Tables: the exercises use the students (id, name, city, marks, course_id) and courses (id, course_name) tables, with the data from our MySQL INSERT chapter. The students are: Robin (Delhi, 85), Maria (Mumbai, 92), John (Pune, 78), Asha (Jaipur, 88), Vikram (Delhi, 65), Neha (Mumbai, 45).

Hint:

• Start from the courses table and use LEFT JOIN.
• Count a column of the students table, like s.id, so that NULL is not counted.

---

MySQL query to count the students of each course

SELECT c.course_name, COUNT(s.id) AS students
FROM courses c
LEFT JOIN students s ON s.course_id = c.id
GROUP BY c.id, c.course_name
ORDER BY c.id;

Output:

How it works

• LEFT JOIN keeps all the courses, even those without students.
• COUNT(s.id) counts only real students. COUNT(*) would give 1 for C Programming.

Read MySQL JOINs.

• Previous Exercise
• Next Exercise

---

Tutorials
No Content Found.

Exercises & Assignments
No Content Found.

Interview Questions & Answers
No Content Found.