7.10 Selecting Only Groups with Certain Characteristics
7.10.1 Problem
You want to calculate group summaries,
but display the results only for those groups that match certain
criteria.
7.10.2 Solution
Use a HAVING clause.
7.10.3 Discussion
You're familiar with the use of
WHERE to specify conditions that individual
records must satisfy to be selected by a query. It's
natural, therefore, to use WHERE to write
conditions that involve summary values. The only trouble is that it
doesn't work. If you want to identify drivers in the
driver_log table who drove more than three days,
you'd probably first think to write the query like
this:
mysql> SELECT COUNT(*), name
-> FROM driver_log
-> WHERE COUNT(*) > 3
-> GROUP BY name;
ERROR 1111 at line 1: Invalid use of group function
The problem here 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
records. That is, HAVING operates on the
already-selected-and-grouped set of rows, applying additional
constraints based on aggregate function results that
aren't known during the initial selection process.
The preceding query therefore should be written like this:
mysql> SELECT COUNT(*), name
-> FROM driver_log
-> GROUP BY name
-> HAVING COUNT(*) > 3;
+----------+-------+
| COUNT(*) | name |
+----------+-------+
| 5 | Henry |
+----------+-------+
When you use HAVING, you can still include a
WHERE clause—but only to select rows, not to
test summary values.
HAVING can refer to aliases, so the previous query
can be rewritten like this:
mysql> SELECT COUNT(*) AS count, name
-> FROM driver_log
-> GROUP BY name
-> HAVING count > 3;
+-------+-------+
| count | name |
+-------+-------+
| 5 | Henry |
+-------+-------+
|
