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

Discussion

GROUP BY, like ORDER BY, can refer to expressions. This means you can use calculations as the basis for grouping. For example, to find the distribution of the lengths of state names, use those lengths as the grouping characteristic:

mysql> SELECT CHAR_LENGTH(name), COUNT(*)     -> FROM states GROUP BY CHAR_LENGTH(name); +-------------------+----------+ | CHAR_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 | +-------------------+----------+ 

As with ORDER BY, you can write the grouping expression directly in the GROUP BY clause, or use an alias for the expression (if it appears in the output column list), and refer to the alias in the GROUP BY.

You can group by multiple expressions if you like. To find days of the year on which more than one state joined the Union, group by statehood month and day, and then use HAVING and COUNT⁠(⁠ ⁠ ⁠) to find the nonunique combinations:

mysql> SELECT     -> MONTHNAME(statehood) AS month,     -> DAYOFMONTH(statehood) AS day,     -> COUNT(*) AS count     -> FROM states GROUP BY month, day HAVING count > 1; +----------+------+-------+ | month    | day  | count | +----------+------+-------+ | February |   14 |     2 | | June     |    1 |     2 | | March    |    1 |     2 | | May      |   29 |     2 | | November |    2 |     2 | +----------+------+-------+