PostgreSQL - HAVING clause

The HAVING clause in PostgreSQL is an essential feature for filtering grouped data that has been aggregated using functions like SUM(), COUNT(), AVG(), and others. Unlike the WHERE clause, which filters rows before aggregation, the HAVING clause is used to filter results after the grouping and aggregation have occurred. This makes it particularly useful when we want to apply conditions to aggregated data.

In this article, we will explain the PostgreSQL HAVING clause in-depth, provide practical examples, and explain how it can be used effectively for complex queries. This will not only help us understand how to use HAVING but also optimize our queries for better performance and accuracy.

What is PostgreSQL HAVING Clause?

The HAVING clause is used to filter the results of a query that includes an aggregate function, such as SUM(), COUNT(), MAX(), or AVG(). It allows us to specify conditions on the grouped results, making it an essential tool for querying aggregated data.

The HAVING clause is always used in conjunction with the GROUP BY clause. It is important to note that HAVING is applied after GROUP BY, and unlike WHERE, it cannot be used independently without GROUP BY.

Syntax

SELECT
    column_1,
    aggregate_function (column_2)
FROM
    tbl_name
GROUP BY
    column_1
HAVING
    condition;

Key Terms

• column_1: This is the column by which you want to group the results.
  
• aggregate_function(column_2): This represents aggregate functions like SUM(), COUNT(), AVG(), etc., applied to column_2.
  
• tbl_name: The name of the table from which you are selecting data.
  
• GROUP BY column_1: This clause groups the rows that have the same values in column_1.
  
• HAVING condition: This clause sets the condition for the groups created by the GROUP BY clause.

PostgreSQL HAVING clause Examples

Let us take a look at some of the examples of HAVING clause in PostgreSQL to better understand the concept and how it can be applied in real-world scenarios to filter aggregated data.

Example 1: Filter Customers Spending More Than 200 USD

Here we will query to selects the only customer who has been spending more than 200 USD using the HAVING clause in the "payment" table of our sample database.

Query:

SELECT
    customer_id,
    SUM (amount)
FROM
    payment
GROUP BY
    customer_id
HAVING
    SUM (amount) > 200;

Output

Explanation:

• This query groups the payments by customer_id and sums up the amount for each customer.
  
• The HAVING clause filters the results, only returning customers who have spent more than 200 USD.

Example 2: Filter Stores with More Than 200 Customers

Here we will query to select the stores that has more than 200 customers using the HAVING clause in the "customer" table of our sample database.

Query:

SELECT
    store_id,
    COUNT (customer_id)
FROM
    customer
GROUP BY
    store_id
HAVING
    COUNT (customer_id) > 200;

Output

Explanation:

• This query groups the customers by store_id and counts the customer_id for each store.
  
• The HAVING clause ensures that only stores with more than 200 customers are returned.

Important Points About PostgreSQL HAVING clause

• Post-Grouping Filtering: The HAVING clause is applied after GROUP BY and before SELECT. It cannot be used without GROUP BY, unlike WHERE, which can be used independently.
  
• Cannot be Used Without GROUP BY: The WHERE clause filters rows before any grouping occurs, while the HAVING clause filters groups after they have been created.
  
• Logical Operators: We can combine multiple conditions in the HAVING clause using logical operators like AND, OR, and NOT.
  
• Column Aliases: We can use column aliases defined in the SELECT clause within the HAVING clause for better readability.

Conclusion

The PostgreSQL HAVING clause is an essential tool for querying and filtering aggregated data, enabling us to apply conditions to groups after aggregation. By using HAVING effectively, we can refine our SQL queries to return more meaningful insights from our data.

Whether we're working with SUM(), COUNT(), or other aggregate functions, the HAVING clause helps us filter results based on aggregated conditions. Understanding how and when to use it will significantly enhance our ability to write efficient, complex queries in PostgreSQL.