Filter Groups Using HAVING

You’ve just written a perfect SUM() or COUNT() query with a GROUP BY, and you’re feeling good. Then you try to add a condition to filter the groups — maybe you want only departments with more than 10 employees — and you write WHERE COUNT(*) > 10. PostgreSQL immediately throws an error: aggregate functions are not allowed in WHERE. That’s the moment you realize: filtering rows is easy, but filtering groups is a completely different skill. In this lesson, you’ll master filter groups using HAVING, the PostgreSQL clause that turns a messy confusion into a clean, powerful query pattern.

The problem this lesson solves

You already know WHERE filters rows before grouping happens. But what do you do when the condition depends on the group itself — like “show only categories with average price above $50” or “list customers who placed more than 3 orders”?

Trying to use WHERE here fails because at the time WHERE is evaluated, PostgreSQL hasn’t yet computed the aggregates. The WHERE clause sees individual rows, not the group totals. That’s a fundamental limitation of the SQL execution order — not a bug you can work around with clever syntax.

Without HAVING, you’re forced into ugly workarounds: subqueries in FROM, CTEs with two passes over the data, or worse, fetching all groups and filtering in application code. Each workaround adds complexity, hurts performance, and makes your query harder to read. Filter groups using HAVING solves this directly — it’s the built-in, idiomatic way to filter aggregated results.

Core concept / mental model

Think of SQL query processing as a pipeline with distinct stages. Here’s a mental model in words:

1. FROM — pull rows from tables.
2. WHERE — drop rows that don’t meet row-level conditions. (No aggregates allowed here.)
3. GROUP BY — pack remaining rows into groups.
4. HAVING — filter the groups themselves, using aggregate or grouped expressions.
5. SELECT — project final columns and expressions.
6. ORDER BY — sort the output.

HAVING is like the bouncer at the door of a club — but the club is the set of groups. Each group must show its ID (the grouped columns) and its aggregate statistics (the COUNT, SUM, AVG, etc.) before it’s allowed into the final result. WHERE is the earlier bouncer checking each individual person (row) before they even form a group.

The single most important rule to remember: WHERE filters rows, HAVING filters groups. If a condition refers to an aggregate function, it must go in HAVING. If it refers to a plain column value that exists in the original row, put it in WHERE — it’s more efficient because it reduces rows before grouping.

How it works step by step

Let’s walk through the logical flow of a query that filters groups using HAVING. Suppose you have an orders table and you want to find customers with more than 5 total orders.

Step 1 — Start with the base query.

Write the SELECT with the columns you want to group by and the aggregate functions you need.

SELECT customer_id, COUNT(*) AS order_count
FROM orders
GROUP BY customer_id;

Step 2 — Add a HAVING clause.

Append HAVING after GROUP BY and specify the aggregate condition.

SELECT customer_id, COUNT(*) AS order_count
FROM orders
GROUP BY customer_id
HAVING COUNT(*) > 5;

Step 3 — Understand the execution order.

PostgreSQL logically processes the query in this order:

• It fetches all rows from orders.
• It groups them by customer_id, computing COUNT(*) for each group.
• It then applies HAVING and keeps only groups where the count exceeds 5.
• Finally, it returns the selected columns.

Step 4 — Combine with WHERE for maximum precision.

You can (and often should) filter rows with WHERE before grouping to avoid wasted work. For example, only consider orders from the last year, then filter groups:

SELECT customer_id, COUNT(*) AS order_count
FROM orders
WHERE order_date >= '2024-01-01'
GROUP BY customer_id
HAVING COUNT(*) > 5;

Pro tip: Always ask yourself: “Does this condition make sense for a single row or for a group?” If it references an aggregate, it belongs in HAVING. If it references a raw column, start with WHERE.

Hands-on walkthrough

Let’s put this into practice with a concrete example. We’ll create a small sales table, populate it, and run queries that filter groups using HAVING.

-- Create a sample table
CREATE TABLE sales (
    id SERIAL PRIMARY KEY,
    product TEXT NOT NULL,
    category TEXT NOT NULL,
    amount NUMERIC(10,2) NOT NULL
);

-- Insert sample data
INSERT INTO sales (product, category, amount) VALUES
    ('Laptop', 'Electronics', 1200.00),
    ('Mouse', 'Electronics', 25.00),
    ('Keyboard', 'Electronics', 75.00),
    ('Monitor', 'Electronics', 300.00),
    ('Desk', 'Furniture', 450.00),
    ('Chair', 'Furniture', 200.00),
    ('Lamp', 'Furniture', 40.00),
    ('Book', 'Stationery', 15.00),
    ('Pen', 'Stationery', 2.00);

Now let’s find categories with more than 2 products:

SELECT category, COUNT(*) AS product_count
FROM sales
GROUP BY category
HAVING COUNT(*) > 2;

Expected output:

   category   | product_count
--------------+---------------
 Electronics  |             4
 Furniture    |             3

Notice that Stationery is excluded because it has only 2 products.

Now let’s find categories with total sales above $500:

SELECT category, SUM(amount) AS total_sales
FROM sales
GROUP BY category
HAVING SUM(amount) > 500;

Expected output:

   category   | total_sales
--------------+-------------
 Electronics  |     1600.00
 Furniture    |      690.00

This time Stationery is dropped because its total is only $17.

Finally, let’s combine WHERE and HAVING — we want categories with more than 2 products, but only counting products priced over $50:

SELECT category, COUNT(*) AS expensive_products
FROM sales
WHERE amount > 50
GROUP BY category
HAVING COUNT(*) > 2;

Expected output:

   category   | expensive_products
--------------+-------------------
 Electronics  |                 3
 Furniture    |                 1

Wait, Furniture has only 1 expensive product — why did it appear? Because we used HAVING COUNT(*) > 2 but the WHERE already reduced the rows, so the count is now of expensive products only. The condition > 2 is still true for Electronics (3) but false for Furniture (1). Let me re-check: Actually, the Furniture group after WHERE has only 1 row, so it should be filtered out. My mistake — let’s correct the query to show the correct result:

Let’s actually run it:

SELECT category, COUNT(*) AS expensive_products
FROM sales
WHERE amount > 50
GROUP BY category
HAVING COUNT(*) > 2;

The correct output is:

   category   | expensive_products
--------------+-------------------
 Electronics  |                 3

Only Electronics has more than 2 expensive products. Furniture has only 1 (Desk at 450), and Stationery has 0.

Pro tip: HAVING evaluates after WHERE, so the aggregate is computed on the filtered rows. This is exactly what you want when you need a condition on aggregated data.

Compare options / when to choose what

When to use a subquery vs HAVING? If you only need to filter on aggregates, always prefer HAVING — it’s cleaner and often faster. Use a subquery when you need to filter on a column that is the result of an aggregate after you’ve already selected it, or when you need to reuse the aggregated value multiple times.

Troubleshooting & edge cases

1. Getting aggregate functions are not allowed in WHERE

This is the classic error. You wrote WHERE COUNT(*) > 5. The fix is to move the condition to HAVING.

Wrong: SELECT category, COUNT(*) FROM sales WHERE COUNT(*) > 2 GROUP BY category;

Right: SELECT category, COUNT(*) FROM sales GROUP BY category HAVING COUNT(*) > 2;

2. Using a column alias in HAVING

You might try HAVING cnt > 2 where cnt is an alias defined in SELECT. PostgreSQL allows this, but it’s a bit obscure and can confuse readers. Prefer the full aggregate expression in HAVING:

-- Works, but less clear
SELECT category, COUNT(*) AS cnt
FROM sales
GROUP BY category
HAVING cnt > 2;

-- Clear and recommended
SELECT category, COUNT(*) AS cnt
FROM sales
GROUP BY category
HAVING COUNT(*) > 2;

Aliases in HAVING are allowed in PostgreSQL, but they’re not standard SQL. Stick to the explicit expression for portability.

3. Empty groups after WHERE

HAVING never sees groups that were eliminated by WHERE. If you filter too aggressively in WHERE, you might get fewer groups than expected. Always double-check your WHERE logic.

4. HAVING without GROUP BY

You can use HAVING without GROUP BY, treating the entire table as a single group. This is useful for conditions like “more than 100 rows exist”:

SELECT COUNT(*)
FROM sales
HAVING COUNT(*) > 100;

This returns one row, either with the count or no rows at all.

5. Case sensitivity in string comparisons

String comparisons in HAVING are case-sensitive by default. Use ILIKE or LOWER() to make them case-insensitive if needed.

What you learned & what's next

You now understand the mental model of query execution, the difference between WHERE and HAVING, and how to filter groups using HAVING with confidence. You practiced on a real table, saw common pitfalls, and know when to choose HAVING over a subquery.

Key takeaways: - WHERE filters rows, HAVING filters groups. - Aggregates are not allowed in WHERE — use HAVING. - Combine WHERE and HAVING for precise filtering. - Use HAVING without GROUP BY to filter the whole table as one group. - Always put conditions on aggregated values in HAVING.

Next in the PostgreSQL Tutorial track, you’ll learn how to sort groups using ORDER BY with aggregates, and then move on to combining HAVING with ORDER BY and LIMIT to build even more powerful reports. Get ready to turn your data into insights!