Database Reference
In-Depth Information
| 7 | 4 |
| 8 | 2 |
| 9 | 5 |
| 10 | 2 |
+-------+--------+
Expressing the results in percentages rather than counts yields relative frequency dis‐
tribution. To show each count as a percentage of the total, use one query to get the total
number of observations and another to calculate the percentages for each group:
mysql>
SET @n = (SELECT COUNT(score) FROM testscore);
mysql>
SELECT score, (COUNT(score)*100)/@n AS percent
->
FROM testscore GROUP BY score;
+-------+---------+
| score | percent |
+-------+---------+
| 4 | 10.0000 |
| 5 | 5.0000 |
| 6 | 20.0000 |
| 7 | 20.0000 |
| 8 | 10.0000 |
| 9 | 25.0000 |
| 10 | 10.0000 |
+-------+---------+
The distributions just shown summarize the number of values for individual scores.
However, if the dataset contains a large number of distinct values and you want a dis‐
tribution that shows only a small number of categories, you may want to lump values
into categories and produce a count for each category.
Recipe 8.10
discusses “lump‐
ing” techniques.
One typical use of frequency distributions is to export the results for use in a graphing
program. But MySQL itself can generate a simple ASCII chart as a visual representation
of the distribution. To display an ASCII bar chart of the test score counts, convert the
counts to strings of
*
characters:
mysql>
SELECT score, REPEAT('*',COUNT(score)) AS 'count histogram'
->
FROM testscore GROUP BY score;
+-------+-----------------+
| score | count histogram |
+-------+-----------------+
| 4 | ** |
| 5 | * |
| 6 | **** |
| 7 | **** |
| 8 | ** |
| 9 | ***** |
| 10 | ** |
+-------+-----------------+
To chart the relative frequency distribution instead, use the percentage values:
