Count Student Number in Departments
You can accomplish this by performing a LEFT JOIN
between the Department
table and the Student
table on the dept_id
column. By using the LEFT JOIN
, we will include all departments, even those without any students. Then, you can use the COUNT
function to calculate the number of students in each department and order the results as required.
Here’s the query:
|
|
Explanation:
- The
LEFT JOIN
ensures that even departments with no students are included in the result. - The
COUNT(s.student_id)
function counts the number of students in each department. - The
GROUP BY
clause groups the results by department, allowing the count to be calculated for each department. - The
ORDER BY
clause sorts the result by the number of students in descending order, and in case of a tie, it sorts by the department name alphabetically.