How to sort SQL query by if statement

Question

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.

Answer 1

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;