Mega Code Archive
Missing and non-missing counts can be determined for subgroups as well
<![CDATA[
mysql>
mysql>
mysql> CREATE TABLE expt
-> (
-> subject VARCHAR(10),
-> test VARCHAR(5),
-> score INT
-> );
Query OK, 0 rows affected (0.01 sec)
mysql> INSERT INTO expt (subject,test,score) VALUES('Jane','A',47);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO expt (subject,test,score) VALUES('Jane','B',50);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO expt (subject,test,score) VALUES('Jane','C',NULL);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO expt (subject,test,score) VALUES('Jane','D',NULL);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO expt (subject,test,score) VALUES('Marvin','A',52);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO expt (subject,test,score) VALUES('Marvin','B',45);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO expt (subject,test,score) VALUES('Marvin','C',53);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO expt (subject,test,score) VALUES('Marvin','D',NULL);
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> SELECT * FROM expt;
+---------+------+-------+
| 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 |
+---------+------+-------+
8 rows in set (0.00 sec)
mysql>
mysql> SELECT subject,
-> COUNT(*) AS total,
-> COUNT(score) AS 'non-missing',
-> COUNT(*) - COUNT(score) AS missing
-> FROM expt GROUP BY subject;
+---------+-------+-------------+---------+
| subject | total | non-missing | missing |
+---------+-------+-------------+---------+
| Jane | 4 | 2 | 2 |
| Marvin | 4 | 3 | 1 |
+---------+-------+-------------+---------+
2 rows in set (0.00 sec)
mysql>
mysql> drop table expt;
Query OK, 0 rows affected (0.00 sec)
]]>