Introduction to PostgreSQL HAVING clause

TheHAVING clause specifies a search condition for a group. TheHAVING clause is often used with theGROUP BY clause to filter groups based on a specified condition.

The following statement illustrates the basic syntax of theHAVING clause:

SELECT  column1,  aggregate_function (column2)FROM  table_nameGROUP BY  column1HAVING  condition;

In this syntax:

• First, theGROUP BY clause groups rows into groups by the values in thecolumn1.
• Then, theHAVING clause filters the groups based on thecondition.

If a group satisfies the specified condition, theHAVING clause will include it in the result set.

Besides theGROUP BY clause, you can also include other clauses such asJOIN andLIMIT in the statement that uses theHAVING clause.

PostgreSQL evaluates theHAVING clause after theFROM,WHERE,GROUP BY, and before theDISTINCT,SELECT,ORDER BY andLIMIT clauses:

Because PostgreSQL evaluates theHAVING clause before theSELECT clause, you cannot use the column aliases in theHAVING clause.

This restriction arises from the fact that, at the point ofHAVING clause evaluation, the column aliases specified in theSELECT clause are not yet available.

HAVING vs. WHERE

TheWHERE clause filters the rows based on a specified condition whereas theHAVING clause filter groups of rows according to a specified condition.

In other words, you apply the condition in theWHERE clause to the rows while you apply the condition in theHAVING clause to the groups of rows.

PostgreSQL HAVING clause examples

Let’s take a look at thepayment table in thesample database:

1) Using PostgreSQL HAVING clause with SUM function example

The following query uses theGROUP BY clause with theSUM() function to find the total payment of each customer:

SELECT  customer_id,  SUM (amount) amountFROM  paymentGROUP BY  customer_idORDER BY  amountDESC;

Output:

customer_id | amount-------------+--------         148 | 211.55         526 | 208.58         178 | 194.61         137 | 191.62...

The following statement adds theHAVINGclause to select the only customers who have been spending more than200:

SELECT  customer_id,  SUM (amount) amountFROM  paymentGROUP BY  customer_idHAVING  SUM (amount)> 200ORDER BY  amount DESC;

Output:

customer_id | amount-------------+--------         148 | 211.55         526 | 208.58(2 rows)

2) PostgreSQL HAVING clause with COUNT example

See the followingcustomer table from thesample database:

The following query uses theGROUP BY clause to find the number of customers per store:

SELECT  store_id,  COUNT (customer_id)FROM  customerGROUP BY  store_id;

Output:

store_id | count----------+-------        1 |   326        2 |   273(2 rows)

The following statement adds theHAVING clause to select a store that has more than 300 customers:

SELECT  store_id,  COUNT (customer_id)FROM  customerGROUP BY  store_idHAVING  COUNT (customer_id)> 300;

Output:

store_id | count----------+-------        1 |   326(1 row)

Summary

• Use theHAVING clause to specify the filter condition for groups returned by theGROUP BY clause.

Last updated onJanuary 17, 2024

On this page

• Introduction to PostgreSQL HAVING clause
• PostgreSQL HAVING clause examples
• Summary