Statistical Techniques - MySQL - page 521

mysql> SELECT subject, score FROM t ORDER BY subject;
+---------+-------+
| subject | score |
+---------+-------+
| 1 | 38 |
| 2 | NULL |
| 3 | 47 |
| 4 | NULL |
| 5 | 37 |
| 6 | 45 |
| 7 | 54 |
| 8 | NULL |
| 9 | 40 |
| 10 | 49 |
+---------+-------+
COUNT(*) counts the total number of rows, and COUNT(score) counts the number of
nonmissing scores. The difference between the two values is the number of missing
scores, and that difference in relation to the total provides the percentage of missing
scores. Perform these calculations as follows:
mysql> SELECT COUNT(*) AS 'n (total)',
-> COUNT(score) AS 'n (nonmissing)',
-> COUNT(*) - COUNT(score) AS 'n (missing)',
-> ((COUNT(*) - COUNT(score)) * 100) / COUNT(*) AS '% missing'
-> FROM t;
+-----------+----------------+-------------+-----------+
| n (total) | n (nonmissing) | n (missing) | % missing |
+-----------+----------------+-------------+-----------+
| 10 | 7 | 3 | 30.0000 |
+-----------+----------------+-------------+-----------+
As an alternative to counting NULL values as the difference between counts, count them
directly using SUM(ISNULL(score)) . The ISNULL() function returns 1 if its argument
is NULL , zero otherwise:
mysql> SELECT COUNT(*) AS 'n (total)',
-> COUNT(score) AS 'n (nonmissing)',
-> SUM(ISNULL(score)) AS 'n (missing)',
-> (SUM(ISNULL(score)) * 100) / COUNT(*) AS '% missing'
-> FROM t;
+-----------+----------------+-------------+-----------+
| n (total) | n (nonmissing) | n (missing) | % missing |
+-----------+----------------+-------------+-----------+
| 10 | 7 | 3 | 30.0000 |
+-----------+----------------+-------------+-----------+
If values are arranged in groups, occurrences of NULL values can be assessed on a per-
group basis. Suppose that t contains scores for subjects that are distributed among
conditions for two factors A and B, each of which has two levels: