SQL HAVING vs WHERE: Diagnosing and Fixing Aggregation Filter Bugs

You run a query, check the totals, and the numbers look slightly off. You add a filter to remove some rows and the result gets more wrong. Somewhere between GROUP BY and your WHERE clause, SQL is doing something you didn't intend — and it isn't going to tell you about it.

The HAVING vs WHERE confusion is one of the most common sources of silent bugs in analytics SQL. Both clauses filter rows, but they operate at completely different stages of query execution, and mixing them up produces incorrect aggregates without any error message.

What you'll learn

• Exactly when SQL applies WHERE vs HAVING in the execution order
• The three most common aggregation filter bugs and how to reproduce them
• How to read a query's logic to spot misplaced filters at a glance
• Rewrite patterns that make the intent of your filters obvious
• Gotchas with NULL, DISTINCT, and subqueries that compound the problem

How SQL Actually Executes a Query

The order you write SQL clauses is not the order the engine processes them. Understanding the logical execution sequence is the fastest way to internalize when each filter applies.

The logical order is roughly: FROM → JOIN → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT.

WHERE runs before grouping. It filters individual rows out of the dataset before any aggregation happens. If a row is excluded by WHERE, it never contributes to a SUM, COUNT, or AVG.

HAVING runs after grouping. It filters the aggregated result set — entire groups, not individual rows. You can reference aggregate expressions like SUM(amount) inside HAVING, but you cannot do that inside WHERE.

-- Execution mental model
SELECT
    customer_id,
    SUM(order_total) AS total_spent
FROM orders
WHERE status = 'completed'        -- Step 1: filter rows BEFORE grouping
GROUP BY customer_id
HAVING SUM(order_total) > 500     -- Step 2: filter groups AFTER aggregation
ORDER BY total_spent DESC;

That query asks: among completed orders only, which customers have spent more than $500 in total? The WHERE makes sure cancelled orders don't inflate totals. The HAVING makes sure you only see customers who actually crossed the threshold.

Bug #1 — Filtering on an Aggregate in WHERE

This one throws an actual error in most databases, so it's the easiest to catch. But it's worth understanding why it fails.

-- This will error in PostgreSQL, MySQL, SQL Server
SELECT customer_id, SUM(order_total) AS total_spent
FROM orders
WHERE SUM(order_total) > 500   -- ERROR: aggregate not allowed in WHERE
GROUP BY customer_id;

The engine can't evaluate SUM(order_total) in the WHERE clause because grouping hasn't happened yet. There are no groups to sum. The fix is to move the filter to HAVING.

SELECT customer_id, SUM(order_total) AS total_spent
FROM orders
GROUP BY customer_id
HAVING SUM(order_total) > 500;

Some query tools will helpfully tell you