PostgreSQL HAVING

 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 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 HAVINGclause:

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 the 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 the 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 paymenttable 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 the 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

• The HAVING the clause specifies a search condition for a group or an aggregate returned by the GROUP BY clause.