Student, Department Order by both with condition

Question

I am stuck in a query, my query is as follow:

SELECT department_name AS 'Department Name', COUNT(*) AS 'No of Employees' FROM departments INNER JOIN employees ON employees.department_id = departments.department_id GROUP BY departments.department_id, department_name
ORDER BY COUNT(*) DESC;

this query is giving me results as in below attachment

What i want now is to sort the departments alphabetically if two or more department have same number of students. e.g "Purchasing" and "Finance" should be sort alphabetically.

You can try this query in the following link:

Check your query here

Try `ORDER BY COUNT(*) DESC, department_name` – Giorgos Betsos Apr 04 '17 at 08:41 — Giorgos Betsos, Apr 04 '17 at 08:41

score 1 · Accepted Answer · edited May 23 '17 at 12:09

This is simple. You can order by your result set by multiple columns as follows:

 SELECT department_name AS 'Department Name', COUNT(*) AS 'No of Employees'
 FROM departments INNER JOIN employees ON 
 employees.department_id = departments.department_id 
 GROUP BY departments.department_id, department_name
 ORDER BY COUNT(*) DESC, department_name Asc;

The following thread is a good read

SQL Multiple Column Ordering

Hope this helps :)

score 0 · Answer 2 · answered Apr 04 '17 at 08:47

Is this what you are looking for.?

    SELECT department_name AS 'Department Name', COUNT(*) AS 'No of Employees' 
    FROM departments INNER JOIN employees ON employees.department_id = departments.department_id GROUP BY departments.department_id, department_name
    ORDER BY department_name,COUNT(*) DESC;

Student, Department Order by both with condition

2 Answers2