Garden-Variety HAVING

Here's the standard use of HAVING: GROUP BY divides the rows into sets (by type), and HAVING puts a condition on the sets. In this example HAVING eliminates those sets that include only one book:

SQL
select type, count(*)
from titles
group by type
having count(*) > 1
type            count(*)
============ ===========
popular_comp           3
business               4
psychology             5
mod_cook               2
trad_cook              3
[5 rows]

HAVING Restrictions

You couldn't simply substitute WHERE for HAVING in the preceding query because WHERE does not allow aggregates. In addition, most systems require expressions in the HAVING clause to be either

  • An aggregate expression; or

  • A subset of SELECT list expressions.

In terms of allowed elements, HAVING search conditions are identical to WHERE ...

Get Practical SQL Handbook, The: Using SQL Variants, Fourth Edition now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial