CODEWITHSUNDEEP
sqlsql-serverdistinct
alli666
alli666

Reputation: 11

580. Count Student Number in Departments on Leetcode

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;

Upvotes: 0

Views: 1017

Answers (2)

Pranav Sagar
Pranav Sagar

Reputation: 11

If we use left join then the count of law students will not be considered but as in the question, it has stated that we need to show 0 for such a case.

select
     d.dept_name,count(s.student_id) as student_number 
from  
     Student s right join Department d
on
     s.dept_id = d.dept_id
group by 
     d.dept_id
order by
     student_number desc,dept_name

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521178

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;

Upvotes: 4

Related Questions