Database Reference
In-Depth Information
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 to be
summarized, not to test already calculated 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 |
+-------+-------+
8.8. Using Counts to Determine Whether Values Are
Unique
Problem
You want to know whether values in a table are unique.
Solution
Use
HAVING
in conjunction with
COUNT()
.
Discussion
DISTINCT
eliminates duplicates but doesn't show which values actually were duplicated
in the original data. You can use
HAVING
to find unique values in situations to which
DISTINCT
does not apply.
HAVING
can tell you which values were unique or nonunique.
The following statements show the days on which only one driver was active, and the
days on which more than one driver was active. They're based on using
HAVING
and
COUNT()
to determine which
trav_date
values are unique or nonunique:
