Generating Summaries - MySQL - page 288

mysql> SELECT subject, test, score FROM expt ORDER BY subject, test;
+---------+------+-------+
| subject | test | score |
+---------+------+-------+
| Jane | A | 47 |
| Jane | B | 50 |
| Jane | C | NULL |
| Jane | D | NULL |
| Marvin | A | 52 |
| Marvin | B | 45 |
| Marvin | C | 53 |
| Marvin | D | NULL |
+---------+------+-------+
By using a GROUP BY clause to arrange the rows by subject name, the number of tests
taken by each subject, as well as the total, average, lowest, and highest scores, can be
calculated like this:
mysql> SELECT subject,
-> COUNT(score) AS n,
-> SUM(score) AS total,
-> AVG(score) AS average,
-> MIN(score) AS lowest,
-> MAX(score) AS highest
-> FROM expt GROUP BY subject;
+---------+---+-------+---------+--------+---------+
| subject | n | total | average | lowest | highest |
+---------+---+-------+---------+--------+---------+
| Jane | 2 | 97 | 48.5000 | 47 | 50 |
| Marvin | 3 | 150 | 50.0000 | 45 | 53 |
+---------+---+-------+---------+--------+---------+
You can see from the results in the column labeled n (number of tests) that the query
counts only five values, even though the table contains eight. Why? Because the values
in that column correspond to the number of non- NULL test scores for each subject. The
other summary columns display results that are calculated only from the non- NULL
scores as well.
It makes a lot of sense for aggregate functions to ignore NULL values. If they followed
the usual SQL arithmetic rules, adding NULL to any other value would produce a NULL
result. That would make aggregate functions really difficult to use: to avoid getting a
NULL result, you'd have to filter out NULL values every time you performed a summary.
By ignoring NULL values, aggregate functions become a lot more convenient.
However, be aware that even though aggregate functions may ignore NULL values, some
of them can still produce NULL as a result. This happens if there's nothing to summarize,
which occurs if the set of values is empty or contains only NULL values. The following
query is the same as the previous one, with one small difference. It selects only NULL test
scores to illustrate what happens when there's nothing for the aggregate functions to
operate on: