MySQL Cookbook

Selecting Only Groups with Certain Characteristics

Problem

You want to calculate group summaries, but display the results only for those groups that match certain criteria.

Solution

Use a HAVING clause.

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