HAVING

The HAVING clause can be used after the GROUP BY clause to provide filter criteria after the grouping has been completed. In terms of syntax the HAVING clause is identical to the WHERE clause, but while the WHERE clause occurs before the grouping, the HAVING clause occurs after the grouping.

Examples

Count the number of entries in the addresses table that belong to each different city, filtering out cities with a count below 50:

Query

SELECT city, count(*)FROM addressesGROUP BY cityHAVING count(*) >= 50;

Result

 city    | count---------+------- Linden  |    50 Mallard |    51

Compute the average income per city per street_name, filtering out cities with an average income bigger than twice the median income:

Query

SELECT city, street_name, avg(income)FROM addressesGROUP BY city, street_nameHAVING avg(income) > 2 * median(income);

Result

 city    | street_name | avg---------+-------------+----- Linden  | Spruce Blvd |  40 Mallard | Pond Road   |  60

Syntax