Accepted Easy Solution Using Right Join

  • 2
    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

    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.