What can an aggregate function do in the ORDER BY clause?

Question

Lets say I have a plant table:

id fruit
1  banana
2  apple
3  orange

I can do these

SELECT * FROM plant ORDER BY id;
SELECT * FROM plant ORDER BY fruit DESC;

which does the obvious thing.

But I was bitten by this, what does this do?

SELECT * FROM plant ORDER BY SUM(id);
SELECT * FROM plant ORDER BY COUNT(fruit);
SELECT * FROM plant ORDER BY COUNT(*);
SELECT * FROM plant ORDER BY SUM(1) DESC;

All these return just the first row (which is with id = 1).

1. What's happening underhood?
2. What are the scenarios where aggregate function will come in handy in ORDER BY?

Answer 1

Your results are more clear if you actually select the aggregate values instead of columns from the table:

SELECT SUM(id) FROM plant ORDER BY SUM(id)

This will return the sum of all id's. This is of course a useless example because the aggregation will always create only one row, hence no need for ordering. The reason you get a row qith columns in your query is because MySQL picks one row, not at random but not deterministic either. It just so happens that it is the first column in the table in your case, but others may get another row depending on storage engine, primary keys and so on. Aggregation only in the ORDER BY clause is thus not very useful.

What you usually want to do is grouping by a certain field and then order the result set in some way:

SELECT fruit, COUNT(*)
FROM plant
GROUP BY fruit
ORDER BY COUNT(*)

Now that's a more interesting query! This will give you one row for each fruit together with the total count for that fruit. Try adding some more apples and the ordering will actually start making sense:

Complete table:

+----+--------+
| id | fruit  |
+----+--------+
|  1 | banana |
|  2 | apple  |
|  3 | orange |
|  4 | apple  |
|  5 | apple  |
|  6 | banana |
+----+--------+

The query above:

+--------+----------+
| fruit  | COUNT(*) |
+--------+----------+
| orange |        1 |
| banana |        2 |
| apple  |        3 |
+--------+----------+