Published at Jun 26, 2024
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.
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.
The basic syntax of the HAVING clause is as follows:
SELECT column1, aggregate_function(column2)
FROM table
GROUP BY column1
HAVING condition;
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');
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.
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.