MySQL / 7. Формирование итогов

Нахождение наибольшего и наименьшего из итоговых значений

Задача
Вы хотите вычислить итоговые значения для групп и вывести только наибольшее или наименьшее из них.

Решение
Добавьте в запрос инструкцию LIMIT.

Обсуждение
Функции MIN() и MAX() находят граничные значения диапазона, но в данном случае нам нужны экстремумы множества итоговых значений, и эти функции уже не годятся. Аргументами MIN() и MAX() не могут быть другие агрегирующие функции. Например, вы без труда можете вычислить общее количество миль, проделанных каждым водителем:

mysql> SELECT name, SUM(miles)
-> FROM driver_log
-> GROUP BY name;

+-------+--------------+
| name  | SUM(miles) |
+-------+--------------+
| Ben     | 362            |
| Henry | 911            |
| Suzi    | 893             |
+-------+--------------+

Но выбрать только запись для водителя с наибольшим количеством миль так не удастся:

mysql> SELECT name, SUM(miles)
-> FROM driver_log
-> GROUP BY name
-> HAVING SUM(miles) = MAX(SUM(name));
ERROR 1111 at line 1: Invalid use of group function

Вместо этого упорядочим строки, сделав первым наибольшее значение SUM(), и применим инструкцию LIMIT для выбора первой записи:

mysql> SELECT name, SUM(miles) AS 'total miles'
-> FROM driver_log
-> GROUP BY name
-> ORDER BY 'total miles' DESC LIMIT 1;

+-------+------------+
| name | total miles |
+-------+------------+
| Henry | 911          |
+-------+------------+

В инструкции ORDER BY использован псевдоним, так как она не может ссылаться непосредственно на агрегирующие функции.

Обратите внимание, что если существует несколько строк с одинаковым наибольшим итоговым значением, рассмотренный запрос не сообщит вам об этом. Например, можно попробовать установить, какая буква чаще всего является первой в названии штата:

mysql> SELECT LEFT(name,1) AS letter, COUNT(*) AS count FROM states
-> GROUP BY letter ORDER BY count DESC LIMIT 1;

+-------+-------+
| letter | count |
+-------+-------+
| M       | 8        |
+-------+-------+

Но и с буквы N начинаются названия восьми штатов. Если нужны все наиболее часто встречающиеся значения, которых может быть несколько, следует использовать два запроса:

mysql> SELECT LEFT(name,1) AS letter, @max:=COUNT(*) AS count FROM states
-> GROUP BY letter ORDER BY count DESC LIMIT 1;
mysql> SELECT LEFT(name,1) AS letter, COUNT(*) AS count FROM states
-> GROUP BY letter HAVING count = @max;

+-------+-------+
| letter | count |
+-------+-------+
| M       | 8        |
| N       | 8         |
+------+--------+

Статьи по MySQL на эту тему:

Итоги по датам
Одновременная работа с итогами по группам и общим итогом
Формирование отчета, содержащего итоговую информацию и список

Вернуться в раздел: MySQL / 7. Формирование итогов