580 Count Student Number in Departments

Description

Table: Student

• student_id is the primary key (column with unique values) for this table.
• dept_id is a foreign key (reference column) to dept_id in the Department tables.
• Each row of this table indicates the name of a student, their gender, and the id of their department.

Table: Department

• dept_id is the primary key (column with unique values) for this table.
• Each row of this table contains the id and the name of a department.

Problem Statement

Write a solution to report 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).

Return the result table ordered by student_number in descending order. In case of a tie, order them by dept_name alphabetically. The result format is in the following example.

Example 1

Input:

• Student table:

• Department table:

Output:

Solution

This is trivial aggregation problem. One important point to observe in the example is that even if there are no students in a department, we still need to report the department name. So, we can use LEFT JOIN to report the department name even if there are no students.

In below query, I’ve used tmp as a subquery to join the two tables using RIGHT OUTER JOIN and then count the number of students per department.

1WITH tmp AS (
2    SELECT dept_name, student_id FROM Student s
3    RIGHT OUTER JOIN
4    Department d ON s.dept_id = d.dept_id
5) SELECT dept_name, COUNT(student_id) student_number 
6    FROM tmp 
7    GROUP BY dept_name 
8    ORDER BY student_number DESC, dept_name ASC;

If you’re comfortable, you could write this as a single query like below. Notice that I’ve used LEFT OUTER JOIN instead of RIGHT OUTER JOIN in this solution.

1SELECT d.dept_name, COUNT(s.student_id) AS student_number FROM 
2    Department d
3    LEFT OUTER JOIN Student s
4    ON d.dept_id = s.dept_id
5    GROUP BY d.dept_name
6    ORDER BY student_number DESC, dept_name;