sql notes: leetcode#580 count student number in departments

Problem

A university uses 2 data tables, student and department, to store data about its students and the departments associated with each major.

Write a query to print the respective department name and number of students majoring in each department for all departments in the department table (even ones with no current students).

Sort your results by descending number of students; if two or more departments have the same number of students, then sort those departments alphabetically by department name.

The student is described as follow:

where student_id is the student’s ID number, student_name is the student’s name, gender is their gender, and dept_id is the department ID associated with their declared major.

And the department table is described as below:

where dept_id is the department’s ID number and dept_name is the department name.

Here is an example input:
student table:

department table:

The Output should be:

Analysis

Because the problem requires to output number of students in all departments, even if there is no student in a department, we should consider using left join. Left join department table with student table, then group by department name (dept_name) and count number of students in each depatment. Finally, sort by the number of students in descending order and department name alphabetically.

Left join department table with student table:

1
2

SELECT dept_name, student_id FROM
department AS d LEFT JOIN student AS s ON d.dept_id = s.dept_id;

Then count number of students in each department:

1
2
3

SELECT dept_name, COUNT(student_id) AS student_number FROM
department AS d LEFT JOIN student AS s ON d.dept_id = s.dept_id
GROUP BY dept_name;

Finally, sort by number of students in descending order and department name alphabetically:

1
2
3
4

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

Solution

1
2
3
4

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

Link

580. Count Number in Departments
(中文版) SQL 笔记: Leetcode#580 Count Student Number in Departments