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