t_k t_k - 2 months ago 8
MySQL Question

Can not ORDER BY an AVG value with certain GROUP BY criteria in MySQL

I have a table

data_summaries
. It has such columns as
item_id INT(11)
,
user_grouping TEXT
and
value DECIMAL(10,2)
.

If I try to make a query that groups the results by
user_grouping
and orders them by the
AVG
of
value
, that fails:


SELECT user_grouping, AVG(value) AS avg_value, SUM(value) AS sum_value
FROM data_summaries
GROUP BY user_grouping
ORDER BY avg_value
+---------------+-----------+-----------+
| user_grouping | avg_value | sum_value |
+---------------+-----------+-----------+
| London | 50.609733 | 18978.65 |
| Paris | 50.791733 | 19046.90 |
| New York | 51.500400 | 2575.02 |
| NULL | 49.775627 | 18665.86 |
+---------------+-----------+-----------+


The
ORDER BY
clause does seem to be doing something as it does change the order:

SELECT user_grouping, AVG(value) AS avg_value, SUM(value) AS sum_value
FROM data_summaries
GROUP BY user_grouping
+---------------+-----------+-----------+
| user_grouping | avg_value | sum_value |
+---------------+-----------+-----------+
| NULL | 49.775627 | 18665.86 |
| New York | 51.500400 | 2575.02 |
| London | 50.609733 | 18978.65 |
| Paris | 50.791733 | 19046.90 |
+---------------+-----------+-----------+


On the other hand, ordering by the
SUM
of
value
works as expected:

SELECT user_grouping, AVG(value) AS avg_value, SUM(value) AS sum_value
FROM data_summaries
GROUP BY user_grouping
ORDER BY sum_value
+---------------+-----------+-----------+
| user_grouping | avg_value | sum_value |
+---------------+-----------+-----------+
| New York | 51.500400 | 2575.02 |
| NULL | 49.775627 | 18665.86 |
| London | 50.609733 | 18978.65 |
| Paris | 50.791733 | 19046.90 |
+---------------+-----------+-----------+


If I group by
item_id
instead, then ordering by the
AVG
works:

SELECT item_id, AVG(value) AS avg_value, SUM(value) AS sum_value
FROM data_summaries
GROUP BY item_id
+---------+-----------+-----------+
| item_id | avg_value | sum_value |
+---------+-----------+-----------+
| 4 | 49.318225 | 11392.51 |
| 1 | 49.737835 | 11489.44 |
| 2 | 50.420606 | 11647.16 |
| 6 | 51.024242 | 11786.60 |
| 5 | 51.456537 | 11886.46 |
| 3 | 53.213000 | 1064.26 |
+---------+-----------+-----------+


How would I need to change the first query to get it ordered by the average?

Answer

That is a MySQL bug, see Unexpected order for grouped query, that involves avg() in combination with grouping by a text-column. It is still open in MySQL 5.7.15.

As a workaround, you can change your datatype to e.g. varchar. If you don't need indexes to speed it up, casting should work too:

SELECT cast(user_grouping as char(200)), AVG(value) AS avg_value, SUM(value) AS sum_value
FROM data_summaries
GROUP BY cast(user_grouping as char(200))
ORDER BY avg_value