Database Reference
In-Depth Information
| 35 | 1 |
+---------------------------+------------------+
That still isn't correct. The maximum state population was 35,893,799, which should go
into a category for 40 million, not one for 35 million. The problem here is that the
category-generating expression groups values toward the lower bound of each category.
To group values toward the upper bound instead, use the following technique. For
categories of size n , place a value x into the proper category using this expression:
FLOOR(( x +( n -1))/ n )
So the final form of our query looks like this:
mysql> SELECT FLOOR((pop+4999999)/5000000)*5 AS `max population (millions)`,
-> COUNT(*) AS `number of states`
-> FROM states GROUP BY `max population (millions)`;
+---------------------------+------------------+
| max population (millions) | number of states |
+---------------------------+------------------+
| 5 | 28 |
| 10 | 15 |
| 15 | 3 |
| 20 | 2 |
| 30 | 1 |
| 40 | 1 |
+---------------------------+------------------+
The result shows clearly that the majority of US states have a population of five million
or less.
In some instances, it may be more appropriate to categorize groups on a logarithmic
scale. For example, treat the state population values that way as follows:
mysql> SELECT FLOOR(LOG10(pop)) AS `log10(population)`,
-> COUNT(*) AS `number of states`
-> FROM states GROUP BY `log10(population)`;
+-------------------+------------------+
| log10(population) | number of states |
+-------------------+------------------+
| 5 | 7 |
| 6 | 36 |
| 7 | 7 |
+-------------------+------------------+
The query shows the number of states that have populations measured in hundreds of
thousands, millions, and tens of millions, respectively.
You may have noticed that aliases in the preceding queries are written using backticks
(identifier quoting) rather than single quotes (string quoting). Quoted aliases in the
GROUP BY clause must use identifier quoting or the alias is treated as a constant string
expression and the grouping produces the wrong result. Identifier quoting clarifies to
MySQL that the alias refers to an output column. The aliases in the output column list
Search WWH ::




Custom Search