PostgreSQL HAVING

Summary: in this tutorial, you will learn how to use the PostgreSQL HAVING clause to specify a search condition for a group or an aggregate.

Introduction to PostgreSQL HAVING clause

The HAVING clause specifies a search condition for a group. The HAVING clause is often used with the GROUP BY clause to filter groups based on a specified condition.

The following statement illustrates the basic syntax of the HAVING clause:

SELECT 
  column1, 
  aggregate_function (column2) 
FROM 
  table_name 
GROUP BY 
  column1 
HAVING 
  condition;Code language: SQL (Structured Query Language) (sql)

In this syntax:

• First, the GROUP BY clause groups rows into groups by the values in the column1.
• Then, the HAVING clause filters the groups based on the condition.

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

Besides the GROUP BY clause, you can also include other clauses such as JOIN and LIMIT in the statement that uses the HAVING clause.

PostgreSQL evaluates the HAVING clause after the FROM, WHERE, GROUP BY, and before the DISTINCT, SELECT, ORDER BY and LIMIT clauses:

Because PostgreSQL evaluates the HAVING clause before the SELECT clause, you cannot use the column aliases in the HAVING clause.

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

HAVING vs. WHERE

The WHERE clause filters the rows based on a specified condition whereas the HAVING clause filter groups of rows according to a specified condition.

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

PostgreSQL HAVING clause examples

Let’s take a look at the payment table in the sample database:

1) Using PostgreSQL HAVING clause with SUM function example

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

SELECT 
  customer_id, 
  SUM (amount) amount 
FROM 
  payment 
GROUP BY 
  customer_id 
ORDER BY 
  amount DESC;Code language: SQL (Structured Query Language) (sql)

Output:

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

The following statement adds the HAVING clause to select the only customers who have been spending more than 200:

SELECT 
  customer_id, 
  SUM (amount) amount 
FROM 
  payment 
GROUP BY 
  customer_id 
HAVING 
  SUM (amount) > 200 
ORDER BY 
  amount DESC;Code language: SQL (Structured Query Language) (sql)

Output:

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

2) PostgreSQL HAVING clause with COUNT example

See the following customer table from the sample database:

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

SELECT 
  store_id, 
  COUNT (customer_id) 
FROM 
  customer 
GROUP BY 
  store_idCode language: SQL (Structured Query Language) (sql)

Output:

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

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

SELECT 
  store_id, 
  COUNT (customer_id) 
FROM 
  customer 
GROUP BY 
  store_id 
HAVING 
  COUNT (customer_id) > 300;Code language: SQL (Structured Query Language) (sql)

Output:

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

Summary

• Use the HAVING clause to specify the filter condition for groups returned by the GROUP BY clause.