WHERE vs HAVING in SQL: Execution Order & Key Differences

Ask any beginner to calculate total revenue, and they will write a simple SUM(total_amount). But real businesses are not managed with a single grand total. Leaders need revenue broken down per status, per payment method, per region, and per month.

That breakdown requires GROUP BY. And the moment you need to filter those summarized groups—such as finding payment methods with over ₹150,000 in volume—you face the most tested query execution trap in technical interviews: WHERE vs HAVING.

In this guide, we break down GROUP BY mechanics, dissect the exact 7-step logical query execution order that governs all SQL databases, and demonstrate why aliases fail in WHERE clauses. Every query in this guide runs against a live PostgreSQL e-commerce dataset with 2,000 orders and 500 customers, with all metrics verified. For related database foundations, explore our SQL NULL Guide and SQL CTE Guide.

1. GROUP BY Mechanics: Collapsing Rows into Dimensional Summaries

An aggregate function (SUM, AVG, COUNT, MIN, MAX) without a GROUP BY folds the entire table into a single summary row:

SELECT ROUND(SUM(total_amount), 2) AS grand_total
FROM orders;

Result: 782,905.04

To split that total across distinct dimensions, use GROUP BY. The rule of thumb for standard SQL queries: any non-aggregated column in your SELECT list must appear in your GROUP BY clause.

Revenue and Volume by Order Status

Let's group the 2,000 orders in our database by their status:

SELECT status,
       COUNT(*)                   AS orders,
       ROUND(SUM(total_amount), 2) AS revenue,
       ROUND(AVG(total_amount), 2) AS avg_order
FROM orders
GROUP BY status
ORDER BY revenue DESC;

The Output

What the Numbers Reveal

1. One summary row per distinct group: The 2,000 raw rows collapse into exactly 5 status categories.
2. Hidden financial realities: The grand total of ₹782,905.04 hid the fact that ₹99,602.09 was paid out in refunds. Grouping exposes the underlying distribution.
3. Refund averages are negative: In our e-commerce schema, refunds carry negative values (-₹498.01 average), which directly dragged down the overall table average.

2. Grouping by Payment Method

Swapping the dimension column lets you answer a new business question instantly. Let's analyze customer payment preferences:

SELECT payment_method,
       COUNT(*)                   AS orders,
       ROUND(SUM(total_amount), 2) AS revenue
FROM orders
GROUP BY payment_method
ORDER BY revenue DESC;

The Output

Credit card leads transaction volume at ₹173,019.72, while debit card trails at ₹142,990.07. Notice that these totals reflect net volume across all order statuses.

3. The Core Trap: Filtering Groups with HAVING vs WHERE

Suppose your stakeholder asks: "Show me only the payment methods that generated more than ₹150,000 in revenue."

Natural intuition leads most beginners to write a WHERE filter:

-- ❌ THIS CODE THROWS A RUNTIME ERROR
SELECT payment_method,
       ROUND(SUM(total_amount), 2) AS revenue
FROM orders
WHERE SUM(total_amount) > 150000
GROUP BY payment_method;

The Database Error

ERROR: aggregate functions are not allowed in WHERE
LINE 4: WHERE SUM(total_amount) > 150000

The database halts immediately. Why? Because the WHERE clause evaluates individual rows before any grouping or aggregation takes place. At the time WHERE executes, SUM(total_amount) does not exist yet.

The Correct Query: Using HAVING

To filter summarized groups based on aggregate conditions, you must use HAVING:

-- ✅ THE CORRECT WAY: HAVING filters groups
SELECT payment_method,
       ROUND(SUM(total_amount), 2) AS revenue
FROM orders
GROUP BY payment_method
HAVING SUM(total_amount) > 150000
ORDER BY revenue DESC;

The Filtered Result

Exactly 4 rows return. debit_card (₹142,990.07) was below the ₹150,000 threshold and was cleanly discarded after aggregation.

4. Query Execution Order: Why the Difference Exists

SQL syntax is declarative: you write clauses in one order, but the query optimizer executes them in a completely different sequence. Understanding this 7-step sequence makes database errors predictable and intuitive.

┌──────────────────────────────────────────────────────────┐
│             LOGICAL QUERY EXECUTION ORDER                │
├──────────────────────────────────────────────────────────┤
│  1. FROM & JOINS   → Retrieve and combine base tables    │
│  2. WHERE          → Filter individual rows              │
│  3. GROUP BY       → Collapse rows into distinct groups  │
│  4. HAVING         → Filter the aggregated groups        │
│  5. SELECT         → Compute columns, expressions, aliases│
│  6. DISTINCT       → Deduplicate result rows             │
│  7. ORDER BY       → Sort the final rows                 │
│  8. LIMIT / OFFSET → Truncate to the requested window    │
└──────────────────────────────────────────────────────────┘

Why Aliases Fail in WHERE and GROUP BY

Look at where SELECT sits in the pipeline (Step 5). When you write:

SELECT payment_method,
       SUM(total_amount) AS total_rev -- alias created at Step 5
FROM orders                           -- Step 1
WHERE total_rev > 150000              -- ❌ Step 2: total_rev does NOT exist yet!
GROUP BY payment_method;              -- Step 3

Because WHERE runs at Step 2 and GROUP BY runs at Step 3, the alias total_rev defined in SELECT (Step 5) has not been compiled.

However, ORDER BY executes at Step 7 (after SELECT). Therefore, sorting by an alias works seamlessly:

ORDER BY revenue DESC -- ✅ Step 7: revenue alias is fully available

5. Combining WHERE and HAVING in Production Queries

In professional data analysis, you almost always use WHERE and HAVING together in the same query:

1. Use WHERE to strip out irrelevant rows early (reducing memory usage and compute cost).
2. Use GROUP BY to summarize the remaining rows.
3. Use HAVING to discard groups that do not meet your business threshold.

Example: High-Performing Payment Methods for Completed Orders Only

SELECT payment_method,
       COUNT(*)                   AS completed_orders,
       ROUND(SUM(total_amount), 2) AS clean_revenue
FROM orders
WHERE status = 'completed'           -- Step 2: filter out refunds & cancellations first
GROUP BY payment_method              -- Step 3: group remaining rows
HAVING SUM(total_amount) > 100000    -- Step 4: keep payment methods with >₹100k completed
ORDER BY clean_revenue DESC;         -- Step 7: sort output

The Result

By filtering WHERE status = 'completed' upfront, we eliminated the negative refund balances, ensuring clean_revenue represents true realized earnings.

6. Aggregates and NULLs: Three Flavors of COUNT

A major source of aggregation confusion in technical assessments is how SQL aggregates handle NULL values. Let's observe the behavior across our 2,000-order table and 800-row reviews table.

SELECT COUNT(*)                    AS all_rows,
       COUNT(shipped_date)         AS has_ship_date,
       COUNT(DISTINCT customer_id) AS unique_customers
FROM orders;

The Three COUNTs

• COUNT(*) (2,000): Counts all rows, regardless of whether any column contains NULL.
• COUNT(column) (1,198): Counts only rows where shipped_date is NOT NULL (meaning 802 orders have not yet shipped or were cancelled).
• COUNT(DISTINCT column) (544): Counts distinct non-NULL customer IDs. (Note: Our dataset contains 500 customers in the customer table, exposing 44 orphaned customer IDs in orders).

The Average Rating Trap

In our reviews table (800 rows), 86 reviews have a NULL rating:

SELECT COUNT(*)                           AS all_reviews,
       COUNT(rating)                      AS rated_reviews,
       ROUND(AVG(rating), 3)              AS avg_real,
       ROUND(AVG(COALESCE(rating, 0)), 3) AS avg_zeroed
FROM reviews;

The Output

AVG(rating) skips the 86 NULL rows and calculates the average over the 714 rated reviews (2.941). If you mistakenly replace NULL with 0 using COALESCE, you invent 86 fake 0-star ratings and depress your true customer rating down to 2.625.

7. WHERE vs HAVING: Feature Comparison

8. Summary Checklist & Practice

When writing aggregation queries, follow this mental checklist:

1. Grain check: What does one row represent in your output? Ensure every unaggregated column in SELECT appears in GROUP BY.
2. Filter placement: If your condition evaluates row-level fields (e.g. status = 'completed', order_date >= '2024-01-01'), place it in WHERE. If it evaluates a calculated summary (e.g. SUM(...) > 50000, COUNT(*) >= 5), place it in HAVING.
3. Execution order awareness: Remember why aliases fail in WHERE and GROUP BY, and keep ORDER BY for final sorting.

To master SQL aggregations, test execution order traps, and build portfolio-grade skills, practice live queries in our Interactive SQL Practice Sandbox or enroll in the complete Data Analyst Career Track. For foundational tutorials, read our SQL JOIN Fan-Out Guide and SQL Date Functions Guide.