PostgreSQL HAVING - Filter groups

This article describes how to use the HAVING clause specify filter conditions for grouped queries.

In PostgreSQL, the HAVING clause is used to specify filter conditions for grouped queries with the GROUP BY clause.

The HAVING is similar to WHERE. Although they both specify filter conditions, but their difference is: the conditions specified by the WHERE clause is used to filter rows in the table, while the conditions specified by the HAVING clause are used to filter groups.

PostgreSQL HAVING syntax

The PostgreSQL HAVING clause must be used with the GROUP BY clause. The following are typical usages of the HAVING clause:

SELECT column1[, column2, ...], aggregate_function(ci)
FROM table
[WHERE clause]
GROUP BY column1[, column2, ...];
HAVING clause

Explanation:

  • The GROUP BY clause is used to specify the column or expression to use for grouping.
  • The HAVING clause is used to filter the grouped data. It requires a logical expression as a condition, where the column name or expression in the logical expression can only be the column, expression, or aggregate function applied to GROUP BY clause.
  • You cannot use column aliases in the HAVING clause.

PostgreSQL HAVING Examples

We will use the tables in the Sakila sample database for demonstration, please install the Sakila sample database in PostgreSQL first.

Example for PostgreSQL HAVING clause and the COUNT function

If you wanted to find the number of films for each film rating from the film table, use the following statement:

SELECT rating, count(*)
FROM film
GROUP BY rating
ORDER BY count(*) DESC;

 rating | count
--------+-------
 PG-13  |   223
 NC-17  |   210
 R      |   195
 PG     |   194
 G      |   178
(5 rows)

This example returns the number of films for all film ratings. If you want to find ratings for films with more than 200 films, use the HAVING clause, as following:

SELECT rating, count(*)
FROM film
GROUP BY rating
HAVING count(*) > 200
ORDER BY count(*) DESC;

 rating | count
--------+-------
 PG-13  |   223
 NC-17  |   210
(2 rows)

Example for PostgreSQL HAVING clause with the SUM function

The following example uses GROUP BY clause, HAVING clause, and the sum() function to find customers with a total spend of more than $180 from the payment table.

SELECT customer_id, sum(amount) total
FROM payment
GROUP BY customer_id
HAVING sum(amount) > 180
ORDER BY total DESC;

 customer_id | total
-------------+--------
         526 | 221.55
         148 | 216.54
         144 | 195.58
         178 | 194.61
         137 | 194.61
         459 | 186.62
(6 rows)

In this example, the steps of execution is as follows:

  1. First, the GROUP BY clause group all rows by the customer_id column.
  2. Then, use the aggregate function sum(amount) to sum the amount columns of all rows in each group and use total as the column alias.
  3. Then, use the HAVING clause to filter the groups that meets sum(amount) is greater than 180.
  4. Finally, use the ORDER BY clause to sort groups in descending order.

Conclusion

PostgreSQL HAVING clause is used to specify filter conditions for grouped queries with GROUP BY clauses.