Skip to Main Content
MySQL Cookbook
book

MySQL Cookbook

by Paul DuBois
October 2002
Intermediate to advanced content levelIntermediate to advanced
1024 pages
27h 26m
English
O'Reilly Media, Inc.
Content preview from MySQL Cookbook

Controlling Summary Display Order

Problem

You want to sort the result of a summary query.

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 query. 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, run this query:

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 | +-------+------+---------+ ...
Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Start your free trial

You might also like

MySQL Reference Manual

MySQL Reference Manual

Michael Widenius, David Axmark, Kaj Arno
High Performance MySQL

High Performance MySQL

Jeremy D. Zawodny, Derek J. Balling
MySQL Stored Procedure Programming

MySQL Stored Procedure Programming

Guy Harrison, Steven Feuerstein

Publisher Resources

ISBN: 0596001452Catalog PageErrata