Generating Summaries - MySQL - page 294

+------------+---------------------+
| COUNT(pop) | COUNT(DISTINCT pop) |
+------------+---------------------+
| 50 | 50 |
+------------+---------------------+
In situations like this, in which values do not group nicely into a small number of sets,
use a transformation that forces them into categories. Begin by determining the range
of population values:
mysql> SELECT MIN(pop), MAX(pop) FROM states;
+----------+----------+
| MIN(pop) | MAX(pop) |
+----------+----------+
| 563626 | 37253956 |
+----------+----------+
You can see from that result that if you divide the pop values by five million, they'll group
into eight categories—a reasonable number. (The category ranges will be 1 to 5,000,000,
5,000,001 to 10,000,000, and so forth.) To put each population value in the proper cat‐
egory, divide by five million, and use the integer result:
mysql> SELECT FLOOR(pop/5000000) AS `max population (millions)`,
-> COUNT(*) AS `number of states`
-> FROM states GROUP BY `max population (millions)`;
+---------------------------+------------------+
| max population (millions) | number of states |
+---------------------------+------------------+
| 0 | 28 |
| 1 | 15 |
| 2 | 3 |
| 3 | 2 |
| 5 | 1 |
| 7 | 1 |
+---------------------------+------------------+
Hmm. That's not quite right. The expression groups the population values into a small
number of categories, but doesn't report the category values properly. Let's try multi‐
plying the FLOOR() results by five:
mysql> SELECT FLOOR(pop/5000000)*5 AS `max population (millions)`,
-> COUNT(*) AS `number of states`
-> FROM states GROUP BY `max population (millions)`;
+---------------------------+------------------+
| max population (millions) | number of states |
+---------------------------+------------------+
| 0 | 28 |
| 5 | 15 |
| 10 | 3 |
| 15 | 2 |
| 25 | 1 |