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 clausebut 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 |
+-------+-------+