HAVING vs WHERE: Filtering Aggregated Data Without Confusion

A WHERE clause filters rows before grouping. A HAVING clause filters groups after aggregation. Most errors come from forgetting this ordering, usually by putting SUM() or COUNT() inside a WHERE clause and getting a syntax error. This guide explains the execution order that decides which clause you need, then shows SQL, pandas, and PySpark implementations you can copy.

Quick Takeaways

• WHERE filters individual rows before GROUP BY. It cannot reference aggregate functions.
• HAVING filters groups after aggregation. It can reference SUM(), AVG(), COUNT(), MIN(), and MAX().
• Put non-aggregate conditions in WHERE. This shrinks the dataset early and cuts compute cost.
• In pandas, WHERE maps to boolean masking or .query(), and HAVING maps to .groupby().filter() or a post-aggregation mask.

Why the Two Clauses Exist: SQL Logical Execution Order

SQL does not run in the order you write it. The engine evaluates clauses in a fixed logical order:

When WHERE runs (step 2), no groups exist yet, so there is no group total to compare against. HAVING runs at step 4, after the engine has computed the aggregates. That ordering is the whole difference.

This order also explains a second rule. Because SELECT runs after HAVING, column aliases defined in SELECT are not guaranteed to be visible in HAVING. More on that below.

Sample Dataset

The examples below use a small orders table.

-- Create a sample table for the examples
CREATE TABLE orders (
    order_id     INT PRIMARY KEY,
    customer_id  INT,
    region       VARCHAR(20),
    status       VARCHAR(20),
    amount       DECIMAL(10, 2),
    order_date   DATE
);

INSERT INTO orders VALUES
(1, 101, 'EMEA', 'completed', 1200.00, '2026-01-05'),
(2, 101, 'EMEA', 'completed',  800.00, '2026-02-11'),
(3, 102, 'APAC', 'refunded',   450.00, '2026-02-14'),
(4, 103, 'AMER', 'completed', 3000.00, '2026-03-02'),
(5, 103, 'AMER', 'completed', 2500.00, '2026-03-19'),
(6, 104, 'APAC', 'completed',  300.00, '2026-04-07'),
(7, 105, 'EMEA', 'cancelled',  150.00, '2026-04-21'),
(8, 105, 'EMEA', 'completed',  950.00, '2026-05-01');

WHERE: Row-Level Filtering

Use WHERE when the condition applies to a single row and does not depend on any group.

-- Total completed revenue per region
SELECT
    region,
    SUM(amount) AS total_revenue   -- aggregate runs on rows that passed WHERE
FROM orders
WHERE status = 'completed'         -- row filter: cancelled/refunded rows never reach GROUP BY
GROUP BY region;

The engine drops the refunded and cancelled rows first. Then it groups and sums only the survivors.

The Classic Error

Beginners often try this:

-- This FAILS: aggregate functions are not allowed in WHERE
SELECT region, SUM(amount) AS total_revenue
FROM orders
WHERE SUM(amount) > 3000
GROUP BY region;

PostgreSQL returns aggregate functions are not allowed in WHERE. The other major engines raise equivalent errors. The group total does not exist at step 2, so the predicate cannot be evaluated.

HAVING: Group-Level Filtering

Use HAVING when the condition depends on an aggregate.

-- Regions whose completed revenue exceeds 3,000
SELECT
    region,
    SUM(amount) AS total_revenue
FROM orders
WHERE status = 'completed'         -- step 1: filter rows
GROUP BY region                    -- step 2: build groups
HAVING SUM(amount) > 3000;         -- step 3: filter groups by their aggregate

Result:

Wait: EMEA’s total is 2,950, which fails the > 3000 test, so only AMER survives. Always verify aggregate math by running the query. A boundary mistake like this is easy to miss in prose.

Combining WHERE and HAVING

Both clauses can appear in one query. Each does its own job.

-- Customers with at least 2 completed orders worth more than 2,000 in total
SELECT
    customer_id,
    COUNT(*)    AS order_count,
    SUM(amount) AS total_spent
FROM orders
WHERE status = 'completed'                  -- row-level
GROUP BY customer_id
HAVING COUNT(*) >= 2                        -- group-level
   AND SUM(amount) > 2000;                  -- group-level

Common Pitfalls and Edge Cases

1. Non-Aggregate Conditions in HAVING

This query is legal but wasteful:

-- Works, but groups every status before discarding most of them
SELECT region, SUM(amount)
FROM orders
GROUP BY region, status
HAVING status = 'completed';

The engine may aggregate all statuses before discarding groups. Many optimizers push the predicate down automatically, but you should not depend on that. Move the condition to WHERE:

SELECT region, SUM(amount)
FROM orders
WHERE status = 'completed'
GROUP BY region;

2. Column Aliases in HAVING

Alias support differs by engine because SELECT is evaluated after HAVING in the logical order.

For portable code, repeat the aggregate expression in HAVING, or wrap the query in a subquery or CTE.

-- Portable alias filtering with a CTE
WITH region_totals AS (
    SELECT region, SUM(amount) AS total_revenue
    FROM orders
    WHERE status = 'completed'
    GROUP BY region
)
SELECT *
FROM region_totals
WHERE total_revenue > 3000;   -- plain WHERE now works because total_revenue is a real column

3. HAVING Without GROUP BY

If you use HAVING with no GROUP BY, the engine treats the whole table as a single group.

-- Returns a row only if the table has more than 5 completed orders
SELECT COUNT(*) AS completed_orders
FROM orders
WHERE status = 'completed'
HAVING COUNT(*) > 5;

4. NULL Handling

COUNT(*) counts rows. COUNT(column) skips NULLs. A HAVING COUNT(amount) > 2 test can behave differently from HAVING COUNT(*) > 2 when amount has missing values. Pick the one that matches your intent.

5. Modern Alternative: QUALIFY

Snowflake, BigQuery, and Databricks support QUALIFY, which filters on window function results. It is to window functions what HAVING is to aggregates.

-- Keep each customer's most recent order (Snowflake / BigQuery syntax)
SELECT *
FROM orders
QUALIFY ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) = 1;

Performance: Why the Placement Matters

Filtering early reduces the rows that flow into the expensive grouping step.

Two practical rules:

• Rule 1: If a condition can run without knowing the group, put it in WHERE.
• Rule 2: Reserve HAVING for conditions that need an aggregate.

On distributed engines such as Spark or BigQuery, early filtering also reduces the data moved across the network during the shuffle that precedes aggregation.

The Same Logic in pandas

Analysts who move between SQL and Python need the mapping. In pandas, the order of operations is explicit because you write each step in sequence.

import pandas as pd

# Rebuild the sample dataset
df = pd.DataFrame({
    "order_id":    [1, 2, 3, 4, 5, 6, 7, 8],
    "customer_id": [101, 101, 102, 103, 103, 104, 105, 105],
    "region":      ["EMEA", "EMEA", "APAC", "AMER", "AMER", "APAC", "EMEA", "EMEA"],
    "status":      ["completed", "completed", "refunded", "completed",
                    "completed", "completed", "cancelled", "completed"],
    "amount":      [1200, 800, 450, 3000, 2500, 300, 150, 950],
})

# WHERE + GROUP BY + HAVING equivalent
result = (
    df[df["status"] == "completed"]                      # WHERE: boolean mask on rows
      .groupby("region", as_index=False)                 # GROUP BY
      .agg(total_revenue=("amount", "sum"))              # aggregate with named output
      .query("total_revenue > 3000")                     # HAVING: filter on aggregated column
)
print(result)

When you want to keep the original rows that belong to qualifying groups, use GroupBy.filter():

# Keep all completed rows for customers with at least 2 completed orders
qualified = (
    df[df["status"] == "completed"]
      .groupby("customer_id")
      .filter(lambda g: len(g) >= 2)   # group-level predicate, returns original rows
)

groupby().agg() collapses groups to one row each. groupby().filter() preserves row granularity. SQL’s HAVING collapses by default, so use a window function or join back to the original table if you need row-level output.

The Same Logic in PySpark

from pyspark.sql import SparkSession, functions as F

spark = SparkSession.builder.appName("having_vs_where").getOrCreate()

orders = spark.table("orders")

result = (
    orders
    .filter(F.col("status") == "completed")                    # WHERE
    .groupBy("region")                                         # GROUP BY
    .agg(F.sum("amount").alias("total_revenue"))               # aggregation
    .filter(F.col("total_revenue") > 3000)                     # HAVING (alias is a real column now)
)

result.show()

In the DataFrame API, filter() is used for both. Its position relative to groupBy().agg() determines whether it behaves like WHERE or HAVING. Spark’s Catalyst optimizer also pushes eligible row-level predicates down automatically.

Real-World Use Cases

Customer Churn Risk: Repeated Payment Failures

Flag customers with three or more failed payments in the last 90 days.

SELECT customer_id, COUNT(*) AS failed_payments
FROM payments
WHERE status = 'failed'                                  -- row filter
  AND payment_date >= CURRENT_DATE - INTERVAL '90 days'  -- row filter
GROUP BY customer_id
HAVING COUNT(*) >= 3;                                    -- group filter

The two row-level predicates belong in WHERE. The threshold on the count belongs in HAVING.

Fraud Detection: Burst Transactions

Find cards with more than five transactions in a single hour.

SELECT card_id,
       DATE_TRUNC('hour', txn_time) AS txn_hour,
       COUNT(*)                     AS txn_count,
       SUM(amount)                  AS hourly_spend
FROM transactions
WHERE txn_time >= CURRENT_DATE
GROUP BY card_id, DATE_TRUNC('hour', txn_time)
HAVING COUNT(*) > 5;

IoT Sensor Streams: Anomalous Devices

Identify devices whose average temperature in the last day exceeds a safe limit, ignoring readings flagged as invalid.

SELECT device_id, AVG(temperature) AS avg_temp
FROM sensor_readings
WHERE reading_valid = TRUE                       -- drop bad readings before averaging
  AND reading_time >= NOW() - INTERVAL '1 day'
GROUP BY device_id
HAVING AVG(temperature) > 85;

Filtering invalid readings in HAVING would be wrong here. The bad values would already have skewed the average.

Retail: Category Performance Review

Find product categories with at least 50 orders and average order value above 40.

SELECT category,
       COUNT(*)    AS orders,
       AVG(amount) AS avg_order_value
FROM sales
WHERE sale_date BETWEEN '2026-01-01' AND '2026-09-30'
GROUP BY category
HAVING COUNT(*) >= 50
   AND AVG(amount) > 40;

Decision Checklist

Ask one question for each condition: “Can I evaluate this on a single row, without knowing anything about its group?”

FAQ

Can I use WHERE and HAVING in the same query?

Yes. WHERE filters rows first, then GROUP BY forms groups, then HAVING filters those groups. Most production reporting queries use both.

Why does SQL not allow aggregate functions in WHERE?

WHERE executes before GROUP BY, so group totals do not exist yet. The engine cannot compare a row to a value it has not computed. Move the aggregate condition to HAVING.

Is HAVING slower than WHERE?

Usually, yes, when both could express the filter. WHERE reduces the row count before aggregation and can use indexes. HAVING runs after grouping, so the engine does more work first. Many optimizers rewrite non-aggregate HAVING predicates into WHERE, but you should write them correctly yourself.

Can I use a column alias in HAVING?

It depends on the database. MySQL and SQLite allow it. PostgreSQL, SQL Server, and Oracle do not, because SELECT aliases are created after HAVING runs. For portable code, repeat the aggregate expression or use a CTE.