Include zero in COUNT with GROUP BY in MySQL

Question

I'm looking to make a query in MySQL to list all the faculties and their number of students given the following table structure:

My query looks like:

SELECT `f`.`id`, `f`.`name`, COUNT(*) `total`
FROM `student` `s`
INNER JOIN `course` `c` ON `c`.`id` = `s`.`course_id`
LEFT JOIN `faculty` `f` ON `f`.`id` = `c`.`faculty_id`
GROUP BY `c`.`faculty_id`
ORDER BY `f`.`name`

And I'm getting this result:

but I need to get all the faculties, even the ones without registered students.

If I use a LEFT JOIN with the course table I get the same results.

Answer 1

If you want all the faculties; your starting table for the JOIN should be the faculty table. Then do Left joins on the other table accordingly.

Use the following query:

SELECT `f`.`id`, `f`.`name`, COUNT(`s`.`id`) AS `total`
FROM `faculty` AS `f` 
LEFT JOIN `course` AS `c` ON `f`.`id` = `c`.`faculty_id` 
LEFT JOIN `student` AS `s` ON `c`.`id` = `s`.`course_id` 
GROUP BY `f`.`id`, `f`.`name` 
ORDER BY `f`.`name`