---
title: "Understanding HAVING in PostgreSQL (With Examples)"
description: "The PostgreSQL HAVING clause allows you to filter rows after aggregation. Learn how to use it with practical examples."
section: "Postgres basics"
---

> **TimescaleDB is now Tiger Data.**

*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](https://www.timescale.com/learn/postgresql-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 [<u>Postgres basics section</u>](https://www.timescale.com/learn/postgres-basics).