Generating Summaries - MySQL - page 290

+---------+
| 3 |
+---------+
Missing and nonmissing counts can be determined for subgroups as well. The following
query does so for each subject, providing an easy way to assess the extent to which the
experiment has been completed:
mysql> SELECT subject,
-> COUNT(*) AS total,
-> COUNT(score) AS 'nonmissing',
-> COUNT(*) - COUNT(score) AS missing
-> FROM expt GROUP BY subject;
+---------+-------+------------+---------+
| subject | total | nonmissing | missing |
+---------+-------+------------+---------+
| Jane | 4 | 2 | 2 |
| Marvin | 4 | 3 | 1 |
+---------+-------+------------+---------+
8.7. Selecting Only Groups with Certain Characteristics
Problem
You want to calculate group summaries but display results only for groups that match
certain criteria.
Solution
Use a HAVING clause.
Discussion
You're familiar with the use of WHERE to specify conditions that rows must satisfy to be
selected by a statement. It's natural, therefore, to use WHERE to write conditions that
involve summary values. The only trouble is that it doesn't work. To identify drivers in
the driver_log table who drove more than three days, you might write the statement
like this:
mysql> SELECT COUNT(*), name FROM driver_log
-> WHERE COUNT(*) > 3
-> GROUP BY name;
ERROR 1111 (HY000): Invalid use of group function
The problem is that WHERE specifies the initial constraints that determine which rows to
select, but the value of COUNT() can be determined only after the rows have been selected.
The solution is to put the COUNT() expression in a HAVING clause instead. HAVING is
analogous to WHERE , but it applies to group characteristics rather than to single rows.