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