PostgreSQL

PostgreSQL Having Clause

In this tutorial, you will learn how to work with the PostgreSQL HAVING clause to return only sets of rows that meet a specific criterion.

The PostgreSQL having clause is closely used in conjunction with the GROUP BY statement, restricting the result to only the rows that meet the condition of the HAVING clause.

Basic Usage

The most basic syntax for the HAVING clause is:

SELECT col_list FROM TABLE_NAME GROUP BY col HAVING condition;

In the syntax expressed above, the HAVING clause filters the groups, and then the group by clause sorts them by the specified column.

That means only the columns that meet the condition of the HAVING clause will be in the GROUP BY clause.

The HAVING clause has a lower priority than other clauses such as SELECT, WHERE, and GROUP BY.

If you need to include an ORDER BY statement in the query, ensure to add it after the HAVING clause because it has a lower priority than the HAVING clause.

Example 1

Let us illustrate how to use the HAVING clause using a real-world example. For this illustration, we will use the sakila sample database.

SELECT film_id, title, release_year, LENGTH FROM film GROUP BY film_id HAVING SUM(LENGTH) > 150 LIMIT 10;

In the above example, we select the specified columns from the film table where the SUM of the length of each film_id is greater than 150.

The above query should return only the films where the length was greater than 150 GROUPED BY film_id.

Here is an example output:

Example 2

We can also use the HAVING clause with other aggregate functions. An example is the min and max functions.

SELECT payment_id, customer_id, amount FROM payment GROUP BY payment_id HAVING MAX(amount) > 10 LIMIT 5;

The query above should return the values where the amount is greater than 10.

Example 3

You can also use the HAVING clause with the WHERE clause. For example:

SELECT payment_id,
    MAX(amount)
FROM payment
WHERE rental_id > 10000
GROUP BY payment_id
HAVING MAX(amount) < 5 LIMIT 10;

In this case, we group by the payment_id all the records whose maximum value is less than 5 and the rental id is greater than 10000 (determined by the where clause).

In Summary

The PostgreSQL HAVING clause allows you to filter results that satisfy a specific aggregate condition.

About the author

John Otieno

My name is John and am a fellow geek like you. I am passionate about all things computers from Hardware, Operating systems to Programming. My dream is to share my knowledge with the world and help out fellow geeks. Follow my content by subscribing to LinuxHint mailing list