HAVING Clause ​

Use the HAVING clause to filter the results of aggregate functions. (You cannot filter these results in the WHERE clause.)

[ HAVING condition ]

A HAVING condition must either repeat the aggregate function expression that was used in the SELECT list (if the expression appears there) or it can reference the column alias defined for the expression. The HAVING condition may not refer to an expression with an ordinal number. The HAVING clause may introduce an aggregate function that is not referenced elsewhere in the query.

For example, find the cities that have total stadium capacities that are greater than 50000:

premdb=# select city, sum(capacity) cap 
from team 
group by city 
having cap>50000 
order by 2 desc;
   city    |  cap   
------------+--------
 London     | 296983
 Manchester | 130732
 Birmingham |  99801
 Liverpool  |  84963
 Sheffield  |  72434
 Newcastle  |  52405
(6 rows)

Note: You may specify column aliases in the HAVING clause. In this example, you can write having cap>50000. You do not have to repeat the function expression sum(capacity).

The following example introduces an aggregate function in the HAVING clause. The query asks for total stadium capacities for cities where the average capacity is greater than 33000.

premdb=# select city, sum(capacity) cap 
from team 
group by city 
having avg(capacity)>33000 
order by 2 desc;
    city      |  cap   
---------------+--------
 Manchester    | 130732
 Birmingham    |  99801
 Liverpool     |  84963
 Sheffield     |  72434
 Newcastle     |  52405
 Sunderland    |  49000
 Leeds         |  39460
 Middlesbrough |  34742
 Derby         |  33597
 Cardiff       |  33280
(10 rows)

You can express complex conditions in the HAVING clause just as you would in the WHERE clause. For example, you can use AND:

premdb=# select city, sum(capacity) cap 
from team 
group by city 
having avg(capacity)>33000 and sum(capacity)>50000 
order by 2 desc;
   city    |  cap   
------------+--------
 Manchester | 130732
 Birmingham |  99801
 Liverpool  |  84963
 Sheffield  |  72434
 Newcastle  |  52405
(5 rows)

Parent topic:SELECT