LeetCode 580. Count Student Number in Departments SQL Solution

Problem

LeetCode SQL Problem

580. Count Student Number in Departments

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
4	Business

Solution

MySQL

Count Student Number in Departments

-- Count student number in each department
-- Sort results by descending number of students and then department name
SELECT D.dept_name
	,count(S.student_id) AS student_number
FROM department AS D
LEFT JOIN student AS S ON D.dept_id = S.dept_id
GROUP BY D.dept_name
ORDER BY count(S.student_id) DESC
	,D.dept_name

TSQL

Count Student Number in Departments

-- Count student number in each department
-- Sort results by descending number of students and then department name
SELECT D.dept_name
	,count(S.student_id) AS student_number
FROM department AS D
LEFT JOIN student AS S ON D.dept_id = S.dept_id
GROUP BY D.dept_name
ORDER BY count(S.student_id) DESC
	,D.dept_name

Query Output

dept_name	student_number
Engineering	2
Science	1
Business	0
Law	0