Filtering Rows Before and After Aggregation: WHERE vs HAVING in GROUP BY Queries

Introduction

Imagine you're a data analyst at a retail company. Your manager asks: "Which of our sales reps closed more than 10 deals last quarter, but only count deals worth over $500?" That sentence contains two separate filtering requirements — one that applies to individual deals before you start counting, and one that applies to the final count itself. Getting this wrong means your report is silently broken, returning numbers that feel plausible but don't answer the actual question.

This is exactly the problem WHERE and HAVING solve together. They're both filtering clauses in SQL, but they operate at completely different stages of query execution. WHERE acts as a bouncer at the door — it decides which raw rows enter the calculation. HAVING acts as a quality-control inspector at the end of the assembly line — it looks at finished, aggregated results and decides which groups make the cut. Confusing the two is one of the most common sources of subtly wrong SQL, and it trips up beginners and intermediate analysts alike.

By the end of this lesson, you'll know exactly when to use each clause, why the distinction matters for the correctness of your results, and how to combine both in a single query to answer complex business questions confidently.

What you'll learn:

• How SQL processes a GROUP BY query step by step, and where filtering fits in
• The purpose and correct syntax of WHERE for filtering individual rows before aggregation
• The purpose and correct syntax of HAVING for filtering groups after aggregation
• How to combine WHERE and HAVING in one query when you need both
• The most common mistakes — including why you can't use an aggregate in a WHERE clause — and how to fix them

---

Prerequisites

This lesson assumes you're comfortable writing basic SELECT queries with FROM and WHERE, and that you've at least been introduced to aggregate functions like COUNT(), SUM(), and AVG(). If either of those feels shaky, start with SQL Basics: Master SELECT, FROM, WHERE Clauses and Build Your First Queries and Grouping and Summarizing Data: COUNT, SUM, AVG, and GROUP BY for Beginners before continuing here.

---

How SQL Actually Processes a GROUP BY Query

Before we can understand WHERE versus HAVING, we need a clear mental model of how SQL executes a grouped query. The order clauses appear in your written SQL — SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY — is not the order SQL evaluates them. The actual execution order looks like this:

1. FROM — identify the table(s) to read from
2. WHERE — filter individual rows before any grouping happens
3. GROUP BY — collect the remaining rows into groups
4. Aggregate functions — compute COUNT(), SUM(), AVG(), etc., for each group
5. HAVING — filter the groups based on those computed aggregates
6. SELECT — choose which columns and expressions to return
7. ORDER BY — sort the final output

This ordering is the key to everything. WHERE runs in step 2, before the groups even exist. HAVING runs in step 5, after the aggregates have been computed. That's why you can filter on an aggregate in HAVING but not in WHERE — the aggregate simply doesn't exist yet when WHERE runs.

---

The Dataset We'll Use

Throughout this lesson we'll work with a simplified sales database. Here's the schema:

-- Sales representatives
CREATE TABLE reps (
    rep_id    INT PRIMARY KEY,
    rep_name  VARCHAR(100),
    region    VARCHAR(50)
);

-- Individual deals closed by reps
CREATE TABLE deals (
    deal_id    INT PRIMARY KEY,
    rep_id     INT,
    amount     DECIMAL(10, 2),
    closed_date DATE,
    status     VARCHAR(20)   -- 'won', 'lost', 'cancelled'
);

The deals table has about 2,000 rows. Some deals were won, some lost, and some cancelled. Some deals are for large amounts, some are tiny. Some were closed in the current quarter, some are historical. This messiness is realistic — real data always has noise you need to filter out.

---

Filtering Rows Before Aggregation: The WHERE Clause

WHERE has one job: eliminate rows that don't belong in your analysis before the database starts counting or summing anything.

Let's start simple. If you want to count how many deals each rep closed, grouped by rep:

SELECT
    rep_id,
    COUNT(*) AS total_deals
FROM deals
GROUP BY rep_id;

This counts every row in the deals table, including lost and cancelled deals. But what if your manager specifically means won deals? You'd filter those rows out with WHERE:

SELECT
    rep_id,
    COUNT(*) AS won_deals
FROM deals
WHERE status = 'won'
GROUP BY rep_id;

The WHERE status = 'won' clause runs first, discarding every lost and cancelled row entirely. Then GROUP BY creates one group per rep_id from the remaining rows, and COUNT(*) counts only the won deals in each group. The filtering happened to individual rows — no aggregation involved.

You can stack multiple conditions in WHERE just like in any other query:

SELECT
    rep_id,
    COUNT(*)        AS won_deals,
    SUM(amount)     AS total_revenue
FROM deals
WHERE status = 'won'
  AND amount >= 500
  AND closed_date >= '2024-01-01'
GROUP BY rep_id;

Here, before any grouping happens, SQL throws away every row that isn't a won deal, isn't worth at least $500, or isn't from 2024 onward. The aggregates then compute over only the rows that survived all three conditions.

---

Filtering Groups After Aggregation: The HAVING Clause

HAVING looks syntactically similar to WHERE, but it operates on computed groups, not individual rows. Its conditions can — and usually do — reference aggregate functions.

Going back to our example: now we want to find only the reps who closed more than 10 won deals. The number of deals a rep closed doesn't exist as a column in the raw data. It only exists after we count. So WHERE COUNT(*) > 10 is impossible — COUNT() hasn't run yet when WHERE executes. That's where HAVING steps in:

SELECT
    rep_id,
    COUNT(*) AS won_deals
FROM deals
WHERE status = 'won'
GROUP BY rep_id
HAVING COUNT(*) > 10;

Read this query in execution order:

1. Read all rows from deals
2. Keep only rows where status = 'won' (WHERE)
3. Group the remaining rows by rep_id (GROUP BY)
4. Count the rows in each group (COUNT)
5. Keep only groups where that count exceeds 10 (HAVING)
6. Return rep_id and the count for surviving groups

The result will only include reps who had more than 10 won deals. Reps with 10 or fewer won deals are excluded entirely from the output.

You can use any aggregate function in HAVING — SUM(), AVG(), MIN(), MAX(), or COUNT():

SELECT
    rep_id,
    COUNT(*)        AS won_deals,
    SUM(amount)     AS total_revenue,
    AVG(amount)     AS avg_deal_size
FROM deals
WHERE status = 'won'
GROUP BY rep_id
HAVING SUM(amount) > 50000;

This query returns only the reps whose total revenue from won deals exceeded $50,000. Notice we're still using WHERE to pre-filter to won deals. The HAVING clause then acts as a second gate that only the highest-revenue reps pass through.

---

Combining WHERE and HAVING: Both Filters in One Query

The real power — and the real source of confusion — comes when you need both filters simultaneously. Let's answer that opening question: "Which reps closed more than 10 deals last quarter, counting only deals worth over $500?"

SELECT
    rep_id,
    COUNT(*)    AS qualifying_deals,
    SUM(amount) AS qualifying_revenue
FROM deals
WHERE status = 'won'
  AND amount >= 500
  AND closed_date BETWEEN '2024-10-01' AND '2024-12-31'
GROUP BY rep_id
HAVING COUNT(*) > 10
ORDER BY qualifying_revenue DESC;

Let's trace through what this does:

• WHERE removes every deal that wasn't won, wasn't at least $500, or wasn't in Q4 2024. Those rows are gone before any counting begins.
• GROUP BY organizes the surviving rows by rep_id.
• COUNT(*) and SUM(amount) compute totals for each rep's surviving deals.
• HAVING removes any rep whose qualifying deal count is 10 or below.
• ORDER BY sorts the final results by revenue, highest first.

If you accidentally put amount >= 500 in a HAVING clause instead of WHERE, you'd get a completely different (and wrong) result — you'd be filtering on a row-level value at the group level, which some databases allow but which doesn't logically make sense for this question.

---

Why You Can't Use an Aggregate in WHERE

This is the most common error beginners hit, and the error message isn't always obvious. If you try:

-- This will fail
SELECT
    rep_id,
    COUNT(*) AS won_deals
FROM deals
WHERE status = 'won'
  AND COUNT(*) > 10   -- ERROR: aggregate functions not allowed in WHERE
GROUP BY rep_id;

You'll get an error along the lines of "aggregate functions are not allowed in WHERE" in PostgreSQL, or "Invalid use of group function" in MySQL. The database is telling you that it can't evaluate COUNT(*) at the point in execution where WHERE runs — there are no groups yet.

The fix is straightforward: move the aggregate condition to HAVING:

-- Correct version
SELECT
    rep_id,
    COUNT(*) AS won_deals
FROM deals
WHERE status = 'won'
GROUP BY rep_id
HAVING COUNT(*) > 10;

---

A Practical Business Scenario: Regional Sales Report

Let's put it all together with a more involved example. The VP of Sales wants a report: "Show me each region's performance for won deals above $1,000 in 2024, but only include regions where we averaged more than $3,000 per deal and closed at least 20 of them."

This requires joining the deals table to the reps table to get region information. If you need a refresher on joining tables, SQL JOINs Explained with Real-World Examples has you covered.

SELECT
    r.region,
    COUNT(d.deal_id)    AS deal_count,
    SUM(d.amount)       AS total_revenue,
    AVG(d.amount)       AS avg_deal_size
FROM deals d
JOIN reps r ON d.rep_id = r.rep_id
WHERE d.status = 'won'
  AND d.amount > 1000
  AND d.closed_date BETWEEN '2024-01-01' AND '2024-12-31'
GROUP BY r.region
HAVING COUNT(d.deal_id) >= 20
   AND AVG(d.amount) > 3000
ORDER BY total_revenue DESC;

Break it down step by step:

• JOIN connects each deal to its rep's region
• WHERE pre-filters to only won deals over $1,000 from 2024
• GROUP BY r.region creates one group per region
• HAVING applies two aggregate conditions: at least 20 qualifying deals, and an average deal size above $3,000
• ORDER BY presents regions from highest to lowest revenue

Notice how clean the logic is once you know which filter goes where. The business question translates almost directly into SQL clauses. For more complex scenarios involving multiple tables and aggregation, Multi-Table Reporting with JOIN and GROUP BY: Aggregating Across Relationships in a Single Query goes deeper on this pattern.

---

Performance Consideration: Filter Early with WHERE

There's also a performance argument for using WHERE whenever possible. When WHERE discards rows before grouping, the database has fewer rows to organize into groups and fewer values to aggregate. HAVING discards groups only after all that work is done.

Consider a table with 10 million deal rows. If only 200,000 of them are won deals from 2024, a WHERE clause cuts the working set to 200,000 rows before the grouping engine ever touches them. If you tried to handle that with HAVING on a non-aggregate column (some databases allow this), the engine would group all 10 million rows first and then discard most of them — far more expensive.

This is a general principle: filter early. WHERE filters early. Use it for every row-level condition you can. Reserve HAVING exclusively for aggregate conditions where there's no alternative.

---

Hands-On Exercise

Try these queries against the sales database schema described above. You can set up a quick local SQLite database, use a free PostgreSQL instance, or test on any SQL sandbox.

Exercise 1 — WHERE only: Write a query that returns the number of cancelled deals per rep, but only include deals from 2023. Don't filter by deal count yet — just get every rep's cancelled deal total for the year.

Exercise 2 — HAVING only: Starting from a query that counts all deals (any status, any year) per rep, use HAVING to return only reps who have more than 50 total deals on record.

Exercise 3 — Both WHERE and HAVING: Write a query that returns each rep's total revenue from won deals worth more than $2,000, but only show reps who generated more than $100,000 in such deals. Order the results by total revenue descending.

Exercise 4 — Debug this broken query: The following query is intended to find reps who averaged more than $5,000 per won deal, but it throws an error. Identify what's wrong and rewrite it correctly.

SELECT
    rep_id,
    AVG(amount) AS avg_deal
FROM deals
WHERE status = 'won'
  AND AVG(amount) > 5000
GROUP BY rep_id;

---

Common Mistakes & Troubleshooting

Mistake 1: Putting an aggregate in WHERE As covered above, WHERE AVG(amount) > 5000 will throw an error. Move it to HAVING AVG(amount) > 5000.

Mistake 2: Putting a row-level filter in HAVING Writing HAVING status = 'won' instead of WHERE status = 'won' might not throw an error in every database, but it's logically wrong and slower. The database groups all rows first, then checks the status — but since status can vary within a group, the behavior is implementation-defined and unreliable. Always put column-level conditions in WHERE.

Mistake 3: Forgetting that WHERE filters before GROUP BY changes your counts This one is subtle. If you write:

SELECT rep_id, COUNT(*) AS deal_count
FROM deals
GROUP BY rep_id
HAVING COUNT(*) > 10;

...versus:

SELECT rep_id, COUNT(*) AS deal_count
FROM deals
WHERE status = 'won'
GROUP BY rep_id
HAVING COUNT(*) > 10;

The HAVING COUNT(*) > 10 condition in the second query applies to won deals only, because WHERE already filtered out the others. If you expected it to apply to all deals but forgot the WHERE clause changed what's being counted, your threshold might be miscalibrated. Always read your query in execution order to verify what COUNT(*) is actually counting.

Mistake 4: Referencing a SELECT alias in HAVING In PostgreSQL and SQL Server, HAVING deal_count > 10 where deal_count is a SELECT alias will fail. Repeat the full expression: HAVING COUNT(*) > 10. For more details on how aliases interact with query clauses, see Using SQL Aliases Effectively: Naming Columns and Tables for Readable, Maintainable Queries.

Mistake 5: Expecting HAVING without GROUP BY to work consistently You can technically write HAVING without GROUP BY, which treats the entire result as one group. This works in some databases but is rarely what you actually want. If you're filtering a single-group aggregate, that's an unusual pattern — make sure it's intentional.

---

Summary & Next Steps

Here's what you've learned in this lesson:

• SQL executes queries in a specific order: FROM → WHERE → GROUP BY → aggregates → HAVING → SELECT → ORDER BY. Understanding this order is the foundation of everything else.
• WHERE filters individual rows before any grouping or aggregation. Use it for conditions on raw column values: status, dates, amounts, categories.
• HAVING filters groups after aggregation. Use it exclusively for conditions that involve aggregate functions like COUNT(), SUM(), or AVG().
• You can and should use both in the same query when the business question has both types of requirements.
• Filter early for performance: WHERE conditions reduce the data set before expensive grouping operations; HAVING conditions clean up after the fact.

The mental checklist is simple: does my condition involve an aggregate function? If yes → HAVING. If no → WHERE.

Where to go from here: once you're comfortable with the basics of WHERE and HAVING, you'll want to explore more sophisticated patterns. Filtering Groups After Aggregation: Writing HAVING Clauses That Answer Real Business Questions goes deep on complex HAVING patterns, while Combining Aggregates with Conditional Logic: GROUP BY, HAVING, and CASE WHEN in Practice shows you how to layer conditional logic inside your aggregations. When your analysis grows to need multiple layers of filtering and aggregation, Understanding SQL Subqueries: Filtering and Looking Up Data with Nested SELECT Statements will show you how to nest queries inside one another to handle requirements that a single GROUP BY can't cover.