SQL HAVING Clause Explained: Filter Groups After Aggregation

If WHERE filters rows, what filters groups?

That question trips up a lot of people the first time they write aggregation queries. They know how to count orders by customer, but the moment they want to keep only customers with more than 3 orders, they try to put COUNT(*) > 3 into WHERE and the query breaks.

That is exactly what the HAVING clause is for. HAVING filters after rows have already been grouped and aggregated.

In this guide, we will make that timing visible, not just define it. You will see the row-level data collapse into groups first, then you will use HAVING to keep only the groups that meet your condition.

What does HAVING actually do?

HAVING filters grouped results. In other words:

• WHERE decides which rows are allowed into the grouping step.
• HAVING decides which finished groups are allowed into the final result.

Here is the basic pattern:

SELECT customer_id, COUNT(*) AS total_orders
FROM orders
GROUP BY customer_id
HAVING COUNT(*) >= 2;

This query does not ask, "Which individual rows have COUNT(*) >= 2?" That would not even make sense at the row level. It asks, "After the rows are grouped by customer_id, which groups have at least 2 rows?"

Watch the rows collapse into groups first

This animation shows the important mental model. The rows start as individual orders. Then SQL groups them by customer_id and counts how many rows land in each bucket.

Once you see the counts, the HAVING step becomes much easier to understand:

• customer 101 has 2 orders, so it stays
• customer 102 has 1 order, so it gets filtered out
• customer 103 has 3 orders, so it stays

That is why HAVING COUNT(*) >= 2 is a group filter, not a row filter.

Run the difference between WHERE and HAVING

The best way to understand HAVING is to compare it directly with WHERE.

Try these variations next:

SELECT
  customer_id,
  COUNT(*) AS total_orders
FROM orders_having_demo
WHERE order_status = 'paid'
GROUP BY customer_id
HAVING COUNT(*) >= 2
ORDER BY total_orders DESC;

SELECT
  customer_id,
  SUM(amount) AS revenue
FROM orders_having_demo
GROUP BY customer_id
HAVING SUM(amount) >= 200
ORDER BY revenue DESC;

The first variation shows the sequence clearly:

1. WHERE order_status = 'paid' removes non-paid rows first.
2. GROUP BY customer_id builds the groups from the remaining rows.
3. HAVING COUNT(*) >= 2 keeps only groups with enough paid orders.

That order of operations matters more than memorizing syntax.

Why COUNT in WHERE does not work

This is the classic mistake:

-- Wrong
SELECT customer_id, COUNT(*) AS total_orders
FROM orders_having_demo
WHERE COUNT(*) >= 2
GROUP BY customer_id;

The reason it fails is simple: WHERE runs before grouping, so COUNT(*) does not exist yet.

At the WHERE stage, SQL is still looking at individual rows. It has not built customer-level counts. Only after GROUP BY runs do aggregate values like COUNT(*), SUM(amount), or AVG(amount) become available.

Use WHERE and HAVING together, not instead of each other

Many beginners treat WHERE and HAVING as interchangeable. They are not. The strongest grouped queries usually use both.

Use WHERE when you want to reduce the raw rows that enter the grouping step:

SELECT
  customer_id,
  SUM(amount) AS paid_revenue
FROM orders_having_demo
WHERE order_status = 'paid'
GROUP BY customer_id;

Use HAVING when you want to reduce the grouped result after the totals exist:

SELECT
  customer_id,
  SUM(amount) AS paid_revenue
FROM orders_having_demo
WHERE order_status = 'paid'
GROUP BY customer_id
HAVING SUM(amount) >= 150;

This is a good rule of thumb:

• WHERE answers: which raw rows should participate?
• HAVING answers: which finished groups are worth keeping?

Common HAVING patterns

Keep only large groups

SELECT department, COUNT(*) AS headcount
FROM employees
GROUP BY department
HAVING COUNT(*) >= 5;

Keep only groups with enough revenue

SELECT product_category, SUM(amount) AS total_revenue
FROM sales
GROUP BY product_category
HAVING SUM(amount) > 10000;

Keep only groups above the average group metric

SELECT customer_id, SUM(amount) AS total_spend
FROM orders
GROUP BY customer_id
HAVING SUM(amount) > 200;

The exact threshold changes, but the pattern is stable: aggregate first, then filter the grouped summary.

A realistic reporting example

Suppose you are building a dashboard and need a list of cities with at least 2 active customers and at least 3 paid orders combined.

This example is useful because it shows all three layers working together:

• WHERE removes non-paid rows
• GROUP BY city builds one row per city
• HAVING keeps only cities that meet business thresholds

That is much closer to real reporting work than a toy COUNT(*) > 2 example.

Common mistakes to avoid

1. Using HAVING when WHERE should do the job

-- Works, but filters too late
SELECT customer_id, SUM(amount) AS revenue
FROM orders
GROUP BY customer_id
HAVING customer_id > 100;

If the condition does not depend on an aggregate, it often belongs in WHERE instead:

SELECT customer_id, SUM(amount) AS revenue
FROM orders
WHERE customer_id > 100
GROUP BY customer_id;

That is usually clearer and often more efficient because fewer rows need to be grouped.

2. Forgetting the grain of the grouped result

After GROUP BY city, each result row represents a city, not an order. Once you lose track of that, it becomes easy to misread the output.

3. Treating HAVING as "advanced WHERE"

That framing causes confusion. HAVING is not just a more advanced filter. It filters a different thing at a different stage.

Key takeaways

• WHERE filters rows before grouping.
• HAVING filters groups after aggregation.
• Aggregate expressions such as COUNT(*), SUM(amount), and AVG(score) belong naturally in HAVING.
• Strong reporting queries often use both WHERE and HAVING.
• If a condition does not depend on grouped results, check whether it belongs in WHERE instead.

Related Articles

• Mastering SQL GROUP BY: From Basics to Advanced Aggregations for the grouping foundation that makes HAVING meaningful.
• SQL Aggregate Functions: COUNT, SUM, AVG, MIN, MAX Explained for the aggregate functions you most often place inside HAVING.
• From SQL Queries to Analysis: Answering Real Questions with Data for the next step where grouped filters become practical reporting logic.