Database Reference
In-Depth Information
mysql> SET @n = (SELECT COUNT(score) FROM testscore);
mysql> SELECT score,
-> REPEAT('*',(COUNT(score)*100)/@n) AS 'percent histogram'
-> FROM testscore GROUP BY score;
+-------+---------------------------+
| score | percent histogram |
+-------+---------------------------+
| 4 | ********** |
| 5 | ***** |
| 6 | ******************** |
| 7 | ******************** |
| 8 | ********** |
| 9 | ************************* |
| 10 | ********** |
+-------+---------------------------+
The ASCII chart method is crude, obviously, but it's a quick way to get a picture of the
distribution of observations and requires no other tools.
If you generate a frequency distribution for a range of categories where some of the
categories are not represented in your observations, the missing categories do not appear
in the output. To force each category to be displayed, use a reference table and a LEFT
JOIN (a technique discussed in Recipe 14.8 ). For the testscore table, the possible scores
range from 0 to 10, so a reference table should contain each of those values:
mysql> CREATE TABLE ref (score INT);
mysql> INSERT INTO ref (score)
-> VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10);
Then join the reference table to the test scores to generate the frequency distribution.
This query shows the counts as well as the histogram:
mysql> SELECT ref.score, COUNT(testscore.score) AS counts,
-> REPEAT('*',COUNT(testscore.score)) AS 'count histogram'
-> FROM ref LEFT JOIN testscore ON ref.score = testscore.score
-> GROUP BY ref.score;
+-------+--------+-----------+
| score | counts | histogram |
+-------+--------+-----------+
| 0 | 0 | |
| 1 | 0 | |
| 2 | 0 | |
| 3 | 0 | |
| 4 | 2 | ** |
| 5 | 1 | * |
| 6 | 4 | **** |
| 7 | 4 | **** |
| 8 | 2 | ** |
| 9 | 5 | ***** |
| 10 | 2 | ** |
+-------+--------+-----------+
Search WWH ::




Custom Search