August 1, 2023
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.
Table of Contents
Introduction to PostgreSQL HAVING clause
The HAVING
clause specifies a search condition for a group or an aggregate. The HAVING
clause is often used with the GROUP BY
clause to filter groups or aggregates 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;
In this syntax, the group by clause returns rows grouped by the column1
. The HAVING
clause specifies a condition to filter the groups.
It’s possible to add other clauses of the SELECT
statement such as JOIN
, LIMIT
, FETCH
etc.
PostgreSQL evaluates the HAVING
clause after the FROM
, WHERE
, GROUP BY
, and before the SELECT
, DISTINCT
, ORDER BY
and LIMIT
clauses.
Since the HAVING
clause is evaluated before the SELECT
clause, you cannot use column aliases in the HAVING
clause. Because at the time of evaluating the HAVING
clause, the column aliases specified in the SELECT
clause are not available.
HAVING vs. WHERE
The WHERE
clause allows you to filter rows based on a specified condition. However, the HAVING
clause allows you to filter groups of rows according to a specified condition.
In other words, the WHERE
clause is applied to rows while the HAVING
clause is applied to 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 amount of each customer:
SELECT
customer_id,
SUM (amount)
FROM
payment
GROUP BY
customer_id;
The following statement adds the HAVING
clause to select the only customers who have been spending more than 200
:
SELECT
customer_id,
SUM (amount)
FROM
payment
GROUP BY
customer_id
HAVING
SUM (amount) > 200;
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_id
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;
Summary
- Use the
HAVING
clause to specify search condition for a group or an aggregate returned by theGROUP BY
clause.