Grouping by Expression Results
Problem
You want to group rows into subgroups based on values calculated from an expression.
Solution
Put the expression in the
GROUP
BY
clause. For older
versions of MySQL that don’t support
GROUP
BY
expressions, use a
workaround.
Discussion
GROUP
BY
shares the property
with ORDER
BY
that as of MySQL
3.23.2 it can refer to expressions. This means you can use
calculations as the basis for grouping. For example, to find the
distribution of the length of state names, group by
LENGTH(name)
:
mysql>SELECT LENGTH(name), COUNT(*)
->FROM states GROUP BY LENGTH(name);
+--------------+----------+ | LENGTH(name) | COUNT(*) | +--------------+----------+ | 4 | 3 | | 5 | 3 | | 6 | 5 | | 7 | 8 | | 8 | 12 | | 9 | 4 | | 10 | 4 | | 11 | 2 | | 12 | 4 | | 13 | 3 | | 14 | 2 | +--------------+----------+
Prior to MySQL 3.23.2, you cannot use expressions in
GROUP
BY
clauses, so the
preceding query would fail. In Recipe 6.4,
workarounds for this problem were given with regard to
ORDER
BY
, and the same methods
apply to GROUP
BY
. One
workaround is to give the expression an
alias
in the output column list and refer to the alias in the
GROUP
BY
clause:
mysql>SELECT LENGTH(name) AS len, COUNT(*)
->FROM states GROUP BY len;
+------+----------+ | len | COUNT(*) | +------+----------+ | 4 | 3 | | 5 | 3 | | 6 | 5 | | 7 | 8 | | 8 | 12 | | 9 | 4 | | 10 | 4 | | 11 | 2 | | 12 | 4 | | 13 | 3 | | 14 | 2 | +------+----------+
Another is to write the GROUP
BY
clause to refer to the output column ...
Get MySQL Cookbook now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.