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 | +----------+------+-------+
3.145.66.94