580. Count Student Number in Departments on Leetcode-CodePudding

I used SQL Server to work on this problem

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

Output:

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

SELECT 
    d.dept_name,
    COUNT(s.student_id) OVER(PARTITION BY d.dept_name) AS student_number
FROM 
    Department AS d
LEFT JOIN 
    Student AS s ON d.dept_id = s.dept_id
ORDER BY 
    student_number DESC, d.dept_name;

However, I got

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


dept_id student_number
-----------------------
   1        2
   1        2
   2        1
   3        0

DISTINCT() can fix this issue, but I don't understand why we need to use DISTINCT() on distinct values. Could anyone help explain? Thank you

SELECT DISTINCT 
    d.dept_name,
    COUNT(s.student_id) OVER(PARTITION BY d.dept_name) AS student_number
FROM
    Department AS d
LEFT JOIN 
    Student AS s ON d.dept_id = s.dept_id
ORDER BY 
    student_number DESC, d.dept_name;

CodePudding user response：

Just left join the two tables and then use COUNT as an aggregate, not analytic, function.

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