I got two tables that look like this:
STUDENT
ID:Integer
NAME: String
GENDER: Character
DEPT_ID: Integer
DEPARTMENT
ID: Integer
NAME: String
I want to write a query to print the respective department name and number of students majoring in each department for all departments in the Department table (even ones with no current students).
My goal is to sort the results by descending number of student; if 2 or more students have the same number of students, then sort those departments alphabetically by department name.
SELECT DEPARTMENT.NAME, COUNT (STUDENT.ID) AS STUDENT_COUNT FROM DEPARTMENT LEFT JOIN STUDENT ON DEPARTMENT.ID = STUDENT.DEPT_ID GROUP BY DEPARTMENT.NAMR ORDER BY STUDENT-COUNT DESC;
I'm quite new to SQL but this is the best I could come up with. It's not sorting the departments with same name alphabetically.
CodePudding user response：
You can add department name after the student count like this
SELECT DEPARTMENT.NAME, COUNT (STUDENT.ID) AS STUDENT_COUNT FROM DEPARTMENT LEFT JOIN STUDENT ON DEPARTMENT.ID = STUDENT.DEPT_ID GROUP BY DEPARTMENT.NAME ORDER BY STUDENT-COUNT DESC,DEPARTMENT.NAME;
CodePudding user response：
SELECT DEPARTMENT.NAME, COUNT(STUDENT.ID) AS STUDENT_COUNT
FROM DEPARTMENT
LEFT JOIN STUDENT
ON DEPARTMENT.ID = STUDENT.DEPT_ID
GROUP BY DEPARTMENT.NAME
ORDER BY STUDENT_COUNT DESC,DEPARTMENT.NAME ASC;
Below is the link to sqlfiddle with example.
