Statistical Techniques - MySQL - page 516

Solution
Use aggregate functions, but employ a GROUP BY clause to arrange observations into the
appropriate groups.
Discussion
Recipe 15.1 shows how to compute descriptive statistics for the entire set of scores in
the testscore table. To be more specific, use GROUP BY to divide the observations into
groups and calculate statistics for each of them. For example, the subjects in the test
score table are listed by age and sex, so it's possible to calculate similar statistics by age
or sex (or both) by application of appropriate GROUP BY clauses.
Here's how to calculate by age:
mysql> SELECT age, COUNT(score) AS n,
-> SUM(score) AS sum,
-> MIN(score) AS minimum,
-> MAX(score) AS maximum,
-> AVG(score) AS mean,
-> STDDEV_SAMP(score) AS 'std. dev.',
-> VAR_SAMP(score) AS 'variance'
-> FROM testscore
-> GROUP BY age;
+-----+---+------+---------+---------+--------+-----------+----------+
| age | n | sum | minimum | maximum | mean | std. dev. | variance |
+-----+---+------+---------+---------+--------+-----------+----------+
| 5 | 4 | 22 | 4 | 7 | 5.5000 | 1.2910 | 1.6667 |
| 6 | 4 | 27 | 4 | 9 | 6.7500 | 2.2174 | 4.9167 |
| 7 | 4 | 30 | 6 | 9 | 7.5000 | 1.2910 | 1.6667 |
| 8 | 4 | 32 | 6 | 10 | 8.0000 | 1.8257 | 3.3333 |
| 9 | 4 | 35 | 7 | 10 | 8.7500 | 1.2583 | 1.5833 |
+-----+---+------+---------+---------+--------+-----------+----------+
By sex:
mysql> SELECT sex, COUNT(score) AS n,
-> SUM(score) AS sum,
-> MIN(score) AS minimum,
-> MAX(score) AS maximum,
-> AVG(score) AS mean,
-> STDDEV_SAMP(score) AS 'std. dev.',
-> VAR_SAMP(score) AS 'variance'
-> FROM testscore
-> GROUP BY sex;
+-----+----+------+---------+---------+--------+-----------+----------+
| sex | n | sum | minimum | maximum | mean | std. dev. | variance |
+-----+----+------+---------+---------+--------+-----------+----------+
| M | 10 | 71 | 4 | 9 | 7.1000 | 1.7920 | 3.2111 |
| F | 10 | 75 | 4 | 10 | 7.5000 | 1.9579 | 3.8333 |
+-----+----+------+---------+---------+--------+-----------+----------+