Unable to ORDER AVG value with specific GROUP BY criteria in MySQL

Question

I have a table data_summaries. It has columns such as item_id INT(11), user_grouping TEXTand value DECIMAL(10,2).

If I try to make a query that groups the results with user_groupingand orders them by AVG value, this 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 sentence ORDER BYseems to be doing something, as it changes 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 SUMof valueworks 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 instead item_id, then ordering with help AVGworks:

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 do I need to change the first query to sort it by the average?

Answer 1

MySQL, . , avg() text -column. - MySQL 5.7.15.

, . varchar. , , :

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

:

​​ MySQL 5.7.17:

, BLOB AVG(), VAR_POP() STDDEV_POP(), , InnoDB . ( № 22275357, № 79366)