Accepted Easy Solution Using Right Join


  • 2
    P
    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_name 
    ORDER BY student_number DESC, d.dept_name;
    
    

    "ORDER BY d.dept_name" is needed as well (according to the problem description), otherwise you will get a wrong answer.


  • 0
    S

    What if the student has an undeclared major? The dept_id in the student table could be null.


  • 0

    I used Left Join:)

    select department.dept_name, sum(case when student.student_name is not 
    null then 1 else 0 end) as student_number
    from department left join student
    on department.dept_id = student.dept_id
    group by department.dept_name
    order by student_number desc, dept_name asc

Log in to reply
 

Looks like your connection to LeetCode Discuss was lost, please wait while we try to reconnect.