PostgreSQL HAVING

Summary: in this tutorial, you will learn how to use the PostgreSQL HAVING clause to eliminate groups of rows that do not satisfy a specified condition.

Introduction to PostgreSQL HAVING clause

We often use the HAVINGclause in conjunction with the GROUP BY clause to filter group rows that do not satisfy a specified condition.

The following statement illustrates the typical syntax of the HAVINGclause:

The HAVINGclause sets the condition for group rows created by the GROUP BY clause after the GROUP BY clause applies while the WHERE clause sets the condition for individual rows before GROUP BY clause applies. This is the main difference between the HAVINGand WHEREclauses.

In PostgreSQL, you can use the HAVINGclause without the GROUP BY clause. In this case, the HAVINGclause will turn the query into a single group. In addition, the SELECTlist and HAVINGclause can only refer to columns from within aggregate functions. This kind of query returns a single row if the condition in the HAVINGclause is true or zero row if it is false.

PostgreSQL HAVING clause examples

Let’s take a look at the paymenttable in the sample database.

PostgreSQL HAVING clause with SUM function example

The following query gets the total amount of each customer by using the GROUP BY clause:

You can apply the HAVING clause to selects the only customer who has been spending more than 200as the following query:

PostgreSQL HAVING clause with COUNT example

Let’s take a look at the customertable.

The following query returns the number of customers per store:

You can use the HAVINGclause to select store that has more than 300 customers:

In this tutorial, we have shown you how to use the PostgreSQL HAVINGclause to filter groups of rows based on a specified condition.