The PostgreSQL having clause is closely used in conjunction with the GROUP BY statement, restricting the result to only the rows that meet the condition of the HAVING clause.
The most basic syntax for the HAVING clause is:
In the syntax expressed above, the HAVING clause filters the groups, and then the group by clause sorts them by the specified column.
That means only the columns that meet the condition of the HAVING clause will be in the GROUP BY clause.
The HAVING clause has a lower priority than other clauses such as SELECT, WHERE, and GROUP BY.
If you need to include an ORDER BY statement in the query, ensure to add it after the HAVING clause because it has a lower priority than the HAVING clause.
Let us illustrate how to use the HAVING clause using a real-world example. For this illustration, we will use the sakila sample database.
In the above example, we select the specified columns from the film table where the SUM of the length of each film_id is greater than 150.
The above query should return only the films where the length was greater than 150 GROUPED BY film_id.
Here is an example output:
We can also use the HAVING clause with other aggregate functions. An example is the min and max functions.
The query above should return the values where the amount is greater than 10.
You can also use the HAVING clause with the WHERE clause. For example:
WHERE rental_id > 10000
GROUP BY payment_id
HAVING MAX(amount) < 5 LIMIT 10;
In this case, we group by the payment_id all the records whose maximum value is less than 5 and the rental id is greater than 10000 (determined by the where clause).
The PostgreSQL HAVING clause allows you to filter results that satisfy a specific aggregate condition.