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.
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.