Count Student Number in Departments - Problem

You are given two tables: Student and Department.

The Student table contains information about students including their ID, name, gender, and department ID. The Department table contains department information with department ID and name.

Write a SQL query to report the department name and number of students in each department for all departments, even those with no current students.

Requirements:

  • Include all departments from the Department table
  • Count the number of students in each department
  • Order results by student count in descending order
  • If counts are equal, order by department name alphabetically

Table Schema

Student
Column Name Type Description
student_id PK int Primary key, unique student identifier
student_name varchar Name of the student
gender varchar Gender of the student
dept_id int Foreign key referencing Department table
Primary Key: student_id
Department
Column Name Type Description
dept_id PK int Primary key, unique department identifier
dept_name varchar Name of the department
Primary Key: dept_id

Input & Output

Example 1 — Multiple Departments with Students
Input Tables:
Student
student_id student_name gender dept_id
1 Jack M 1
2 Jane F 1
3 Mark M 2
Department
dept_id dept_name
1 Engineering
2 Science
3 Law
Output:
dept_name student_number
Engineering 2
Science 1
Law 0
💡 Note:

Engineering has 2 students (Jack and Jane), Science has 1 student (Mark), and Law has 0 students. Results are ordered by student count descending: 2, 1, 0.
Example 2 — Equal Counts with Alphabetical Ordering
Input Tables:
Student
student_id student_name gender dept_id
1 Alice F 2
2 Bob M 3
Department
dept_id dept_name
1 Mathematics
2 Physics
3 Chemistry
Output:
dept_name student_number
Chemistry 1
Physics 1
Mathematics 0
💡 Note:

Chemistry and Physics both have 1 student each. Since counts are equal, they are ordered alphabetically: Chemistry comes before Physics. Mathematics has 0 students.
Example 3 — All Departments Empty
Input Tables:
Student
student_id student_name gender dept_id
Department
dept_id dept_name
1 Art
2 Music
Output:
dept_name student_number
Art 0
Music 0
💡 Note:

No students exist in the Student table. All departments have 0 students and are ordered alphabetically: Art, Music.

Constraints

  • 1 ≤ student_id, dept_id ≤ 1000
  • student_name and dept_name consist of English letters
  • gender is either 'M' or 'F'
  • Each department in Student table exists in Department table

Visualization

Tap to expand
Count Student Number in Departments INPUT Student Table id name dept_id 1 Alice 1 2 Bob 1 3 Carol 2 4 David 1 Department Table id name 1 Engineering 2 Science 3 Art dept_id references id ALGORITHM STEPS 1 LEFT JOIN Join Department with Student FROM Department d LEFT JOIN Student s 2 JOIN Condition Match on department id ON d.id = s.dept_id 3 GROUP BY Group by department name GROUP BY d.name 4 COUNT & ORDER Count students, sort results COUNT(s.id) DESC, d.name ASC Dept Stud LEFT JOIN FINAL RESULT Output Table dept_name count Engineering 3 Science 1 Art 0 OK - Sorted! By count DESC, name ASC Engineering: Science: Art: (no students) Key Insight: LEFT JOIN ensures ALL departments appear in results, even those with zero students. COUNT(s.id) instead of COUNT(*) correctly returns 0 for departments with no matching students, since NULL values from the LEFT JOIN are not counted. TutorialsPoint - Count Student Number in Departments | Optimal Solution

Related Problems

Asked in
Amazon 28 Microsoft 22 Google 18
34.5K Views
High Frequency
~12 min Avg. Time
892 Likes
Ln 1, Col 1
Smart Actions
💡 Explanation
AI Ready
💡 Suggestion Tab to accept Esc to dismiss
// Output will appear here after running code
Code Editor Closed
Click the red button to reopen