Generating Summaries - MySQL - page 293

Discussion
GROUP BY , like ORDER BY , can refer to expressions. This means you can use calculations
as the basis for grouping. 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 .
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 |
+----------+------+-------+
8.10. Summarizing Noncategorical Data
Problem
You want to summarize a set of values that are not naturally categorical.
Solution
Use an expression to group the values into categories.
Discussion
Recipe 8.9 shows how to group rows by expression results. One important application
for this is to categorize values that are not categorical. This is useful because GROUP BY
works best for columns with repetitive values. For example, you might attempt to per‐
form a population analysis by grouping rows in the states table using values in the pop
column. That doesn't work very well due to the high number of distinct values in the
column. In fact, they're all distinct:
mysql> SELECT COUNT(pop), COUNT(DISTINCT pop) FROM states;