PostgreSQL offers a HAVING clause that is used to specify a specific condition for a group/aggregate. Generally, the HAVING clause is used in conjunction with the GROUP BY clause for filtering the groups based on some particular criteria.

In PostgreSQL, a WHERE clause specifies a condition for table columns; however, the HAVING clause specifies a condition for groups/aggregates.

This write-up will explain how to use the Postgres HAVING clause with the help of some examples. So, let’s begin.

How to Use the HAVING Clause in PostgreSQL?

The first step towards working with Postgres' HAVING clause is to understand its basic syntax:

SELECT
  col1, col2
FROM
  tbl_name
GROUP BY
  col1, col2
HAVING
  condition;

Let’s illustrate the above syntax stepwise:

- Here, col_1, and col_2 are two columns returned by the GROUP BY clause.

- The returned groups will be filtered based on the condition specified within the HAVING clause.

Postgres allows us to use some other clauses of the SELECT query with the HAVING clause, such as ORDER BY, LIMIT, etc.

While specifying multiple clauses, you have to follow the below-given hierarchy:

- FROM, WHERE, and GROUP BY clauses must come before the HAVING clause while the ORDER BY clause and LIMIT clause must come after the HAVING clause.

Example 1: Basic Understanding of GROUP BY Clause:

Let’s execute the SELECT statement to get all the records of the bike_details table:

The output shows that there are ten records in the bike_details table.

Let’s run the following query to get a basic understanding of the GROUP BY clause:

SELECT
  bike_model,
  SUM (bike_price)
FROM
  bike_detials
GROUP BY
  bike_model;

The output shows that the GROUP BY clause eliminated the duplicated records.

Example 2: HAVING Clause with SUM() Function:

In the below-given example, the HAVING clause will fetch only those groups that have bike_price more than 275,000:

SELECT
  bike_model,
  SUM (bike_price)
FROM
  bike_details
GROUP BY
  bike_model
HAVING SUM (bike_price) > 275000;

The output verified that the HAVING clause returned only those groups that satisfied the criteria, i.e., bike_price > 275,000.

Example 3: HAVING Clause with COUNT() Function:

Suppose we have to count the number of groups whose price is greater than 275,000. To do so, we can use the COUNT() function with the collaboration of the HAVING clause and SELECT statement:

SELECT
  bike_model,
  COUNT (bike_price)
FROM
  bike_details
GROUP BY
  bike_model
HAVING SUM (bike_price) > 275000;

The output shows that there are two bikes whose bike_model is 2022.

Conclusion

PostgreSQL provides a HAVING clause that is used to specify a specific condition for a group/aggregate. Generally, the HAVING clause is used in conjunction with the GROUP BY clause for filtering the groups based on some particular criteria. Different functions like SUM(), COUNT(), etc., are used with the HAVING clause to specify a particular search condition. In this write-up, we considered multiple examples to understand the working of the Postgres HAVING clause in a better way.