Count Student Number in Departments

You can accomplish this by performing a LEFT JOIN between the Department table and the Student table on the dept_id column. By using the LEFT JOIN, we will include all departments, even those without any students. Then, you can use the COUNT function to calculate the number of students in each department and order the results as required.

Here’s the query:

1
2
3
4
5
6

SELECT d.dept_name, COUNT(s.student_id) AS student_number
FROM Department d
LEFT JOIN Student s
ON d.dept_id = s.dept_id
GROUP BY d.dept_id, d.dept_name
ORDER BY student_number DESC, d.dept_name ASC;

Explanation:

  • The LEFT JOIN ensures that even departments with no students are included in the result.
  • The COUNT(s.student_id) function counts the number of students in each department.
  • The GROUP BY clause groups the results by department, allowing the count to be calculated for each department.
  • The ORDER BY clause sorts the result by the number of students in descending order, and in case of a tie, it sorts by the department name alphabetically.