580. Count Student Number in Departments

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:

| Column Name  | Type      |
|--------------|-----------|
| student_id | Integer |
| student_name | String |
| gender | Character |
| dept_id | Integer |

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:

| Column Name | Type    |
|-------------|---------|
| dept_id | Integer |
| dept_name | String |

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

Here is an example input:
student table:

| student_id | student_name | gender | dept_id |
|------------|--------------|--------|---------|
| 1 | Jack | M | 1 |
| 2 | Jane | F | 1 |
| 3 | Mark | M | 2 |

department table:

| dept_id | dept_name   |
|---------|-------------|
| 1 | Engineering |
| 2 | Science |
| 3 | Law |

The Output should be:

| dept_name   | student_number |
|-------------|----------------|
| Engineering | 2 |
| Science | 1 |
| Law | 0 |

Solution:

select dept_name, ifnull(student_number, 0) as student_number
from department d
left join
(
select *, count(student_id) as student_number
from student
group by dept_id
) x
on d.dept_id = x.dept_id
group by 1
order by 2 desc, 1 asc;

Link

--

--

--

My homepage to record my thought processes for solving SQL and Algorithm questions

Recommended from Medium

Rollback, Revert, roll-forward, oh my!

A Quick Guide: How To Measure Your Site Performance

Week 10 as an Encora’s Apprentice

AMA (ask me anything) from ColdStack with Stakely.io

Using the GBC Foton30 Laminator for Roll-and-Write Games

Kubernetes Cheatsheet 1 — Setup Minikube

Rapidly Prototyping Engineering Dashboards Using Jupyter Notebooks and Gitlab Pages

Installing Docker on Raspberry Pi| Raspberry PI home server series | Article 1

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Isabelle

Isabelle

In love with telling stories with data

More from Medium

Leetcode SQL

LeetCode SQL| 197. Rising Temperature

SQLNotes: Salaries Differences

SQL Server Join Algorithms — and how to benefit from knowing them