MySQL Cookbook, 2nd Edition

Controlling Summary Display Order

Problem

You want to sort the result of a summary statement.

Solution

Use an ORDER BY clause—if GROUP BY doesn’t produce the desired sort order.

Discussion

In MySQL, GROUP BY not only groups, it sorts. Thus, there is often no need for an ORDER BY clause in a summary statement. But you can still use ORDER BY if you want a sort order other than the one that GROUP BY produces by default. For example, to determine the number of days driven and total miles for each person in the driver_log table, use this statement:

mysql>SELECT name, COUNT(*) AS days, SUM(miles) AS mileage
    -> FROM driver_log GROUP BY name;
+-------+------+---------+
| name  | days | mileage |
+-------+------+---------+
| Ben   |    3 |     362 |
| Henry |    5 |     911 |
| Suzi  |    2 |     893 |
+-------+------+---------+

But that sorts by the names. If you want to sort drivers according to who drove the most days or miles, add the appropriate ORDER BY clause:

mysql>SELECT name, COUNT(*) AS days, SUM(miles) AS mileage
    -> FROM driver_log GROUP BY name ORDER BY days DESC;
+-------+------+---------+
| name  | days | mileage |
+-------+------+---------+
| Henry |    5 |     911 |
| Ben   |    3 |     362 |
| Suzi  |    2 |     893 |
+-------+------+---------+
mysql> SELECT name, COUNT(*) AS days, SUM(miles) AS mileage
    -> FROM driver_log GROUP BY name ORDER BY mileage DESC;
+-------+------+---------+
| name  | days | mileage |
+-------+------+---------+
| Henry |    5 |     911 |
| Suzi  |    2 |     893 |
| Ben   |    3 |     362 |
+-------+------+---------+

The ORDER ...