Count Student Number in Departments


  • 0

    Click here to see the full article post


  • 0
    D

    Why not using inner join instead of outer join

    SELECT
    dept_name, COUNT(*) AS student_number
    FROM
    department
    inner JOIN
    student ON department.dept_id = student.dept_id
    GROUP BY department.dept_name
    ORDER BY student_number DESC , department.dept_name
    ;


  • 0

    Because the 'inner join' will miss the departments with no current students.


  • 0
    S

    SELECT
    dept_name,
    IF(ISNULL(tmp.cnt), 0, tmp.cnt) student_number
    FROM
    department dp1 LEFT JOIN
    (SELECT
    dept_id,
    COUNT(*) cnt
    FROM student
    GROUP BY dept_id) tmp
    ON
    dp1.dept_id = tmp.dept_id
    ORDER BY student_number DESC, dept_name


  • 0
    D

    SELECT
    dept_name
    ,COUNT(DISTINCT(student_id)) AS student_number
    FROM
    department d
    LEFT JOIN student s
    ON d.dept_id=s.dept_id
    GROUP BY
    dept_name
    ORDER BY
    student_number DESC


Log in to reply
 

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