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 ...