Understanding HAVING in PostgreSQL (With Examples)

Written by Team Timescale

When working with PostgreSQL or TimescaleDB, you often need to filter your query results to get meaningful insights. While the WHERE clause is commonly used for this purpose, it can only filter rows before the aggregation. To filter groups of rows after aggregation, PostgreSQL provides the HAVING clause.

Using the PostgreSQL HAVING Clause

The HAVING clause in PostgreSQL is used to filter the results of a GROUP BY operation. It works similarly to the WHERE clause but is applied after the aggregation has taken place. This makes it ideal for conditions that depend on the result of aggregate functions like SUM(), COUNT(), AVG(), etc.

Syntax

The basic syntax of the HAVING clause is as follows:

SELECT column1, aggregate_function(column2) FROM table GROUP BY column1 HAVING condition;

Example table

Let's consider a table sales with the following structure and data:

CREATE TABLE sales ( id SERIAL PRIMARY KEY, region VARCHAR(50), amount NUMERIC, sale_date DATE );

INSERT INTO sales (region, amount, sale_date) VALUES ('North', 5000, '2023-01-15'), ('South', 3000, '2023-02-20'), ('North', 7000, '2023-03-10'), ('East', 2000, '2023-04-12'), ('West', 10000, '2023-05-05'), ('South', 12000, '2023-06-15'), ('East', 3000, '2023-07-18'), ('North', 15000, '2023-08-22'), ('West', 4000, '2023-09-30'), ('South', 5000, '2023-10-25');

Practical examples

Let's dive into some examples to see the HAVING clause in action.

Example 1: Filtering Groups by SUM

Suppose we want to find regions with total sales exceeding $10,000. We can use the HAVING clause with the SUM() function:

SELECT region, SUM(amount) AS total_sales FROM sales GROUP BY region HAVING SUM(amount) > 10000;

Result:

region | total_sales --------+------------- North | 27000 South | 20000 West | 14000

This query groups the rows by region calculates the total sales for each region and filters the groups whose total sales exceed $10,000.

Example 2: Using HAVING with COUNT

Now, let's say we want to find regions with more than two (2) sales records. We can use the COUNT() function in conjunction with the HAVING clause:

SELECT region, COUNT(*) AS number_of_sales FROM sales GROUP BY region HAVING COUNT(*) > 2;

Result:

region | number_of_sales --------+----------------- North | 3 South | 3

This query counts the number of sales records for each region and filters the groups to only include those with more than two sales.

Example 3: Combining WHERE and HAVING

It's common to use both WHERE and HAVING clauses in a single query. For instance, if we want to find regions with total sales over $10,000 only for sales made in the year 2023, we can combine both clauses:

SELECT region, SUM(amount) AS total_sales FROM sales WHERE date_part('year', sale_date) = 2023 GROUP BY region HAVING SUM(amount) > 10000;

Result:

region | total_sales --------+------------- North | 27000 South | 20000 West | 14000

In this example, the WHERE clause filters the rows to include only those from 2023 before the grouping occurs. The HAVING clause then filters the groups based on the total sales.

Next Steps

The HAVING clause is a powerful tool in PostgreSQL for filtering grouped data. It allows you to apply conditions on aggregated data, which is not possible with the WHERE clause alone. By understanding and utilizing the HAVING clause, you can write more precise and effective queries.

To learn more about PostgreSQL clauses and other basics, check out our Postgres basics section.