Skip to content
Ephizen logo ephizen / wiki
CS 303

HAVING vs WHERE

WHERE filters rows before grouping; HAVING filters groups after. Mixing them up is the classic beginner bug.

SQL I — Reading Data beginner #sql #aggregation

TL;DR

  • WHERE filters individual rows before GROUP BY runs. It cannot reference aggregates, because the aggregates don’t exist yet.
  • HAVING filters groups after GROUP BY runs. It can (and almost always does) reference aggregates.

Same predicate machinery, different stage in the pipeline. The mistake beginners make: trying to put SUM(total) > 100 in WHERE, getting a cryptic “aggregate functions are not allowed in WHERE” error, and panicking. The fix is one keyword.

A worked example

CREATE TABLE orders (
    id int, user_id int, country text, total numeric, placed date
);
INSERT INTO orders VALUES
  (1, 1, 'US', 19.99, '2026-04-01'),
  (2, 1, 'US', 42.00, '2026-04-15'),
  (3, 2, 'US',  7.50, '2026-04-20'),
  (4, 3, 'UK', 99.00, '2026-04-22'),
  (5, 3, 'UK', 12.00, '2026-04-25'),
  (6, 4, 'IN',  5.00, '2026-04-28');

Goal. “Per country, total revenue from orders >= $10. Only show countries whose qualifying revenue is at least $50.”

SELECT country, SUM(total) AS qualifying_revenue
FROM   orders
WHERE  total >= 10                       -- per-row filter
GROUP  BY country
HAVING SUM(total) >= 50                  -- per-group filter
ORDER  BY qualifying_revenue DESC;

Step-by-step:

  1. Source rows — 6 rows in orders.
  2. WHERE total >= 10 — drops rows 3 (7.50) and 6 (5.00). 4 rows remain.
  3. GROUP BY country — three groups: US (19.99 + 42.00 = 61.99), UK (99.00 + 12.00 = 111.00), no IN group (its only row was filtered out by WHERE).
  4. HAVING SUM(total) >= 50 — both groups pass (61.99 and 111.00).
  5. Result.
+---------+--------------------+
| country | qualifying_revenue |
+---------+--------------------+
| UK      |       111.00       |
| US      |        61.99       |
+---------+--------------------+

What if you’d put the aggregate predicate in WHERE?

-- ERROR: aggregate functions are not allowed in WHERE
SELECT country, SUM(total) FROM orders WHERE SUM(total) >= 50 GROUP BY country;

The error is the database protecting you: WHERE runs row-by-row before groups exist, so SUM(total) is meaningless there.

What if you put the row predicate in HAVING?

-- Works, but inefficient
SELECT country, SUM(total)
FROM   orders
GROUP  BY country
HAVING SUM(CASE WHEN total >= 10 THEN total ELSE 0 END) >= 50;

You’d have to fold the per-row condition into the aggregate. Possible but ugly. The clean separation — row filter in WHERE, group filter in HAVING — is also the fast one: the planner pushes WHERE down to the storage layer, often using an index, before any grouping happens.

The execution-order picture

FROM
  ↓
WHERE         ← per-row filter (uses indexes)
  ↓
GROUP BY      ← bucket rows
  ↓
HAVING        ← per-group filter
  ↓
SELECT        ← compute output expressions
  ↓
ORDER BY
  ↓
LIMIT

This is the logical order. The planner can rearrange physical execution (it can push HAVING-equivalent conditions down sometimes) as long as results match.

When HAVING is what you want

  • “Customers with at least 5 orders”: HAVING COUNT(*) >= 5.
  • “Categories whose revenue is over $1k”: HAVING SUM(revenue) > 1000.
  • “Buckets where the conversion rate exceeds 10%”: HAVING AVG(CASE WHEN converted THEN 1.0 ELSE 0 END) > 0.10.
  • “Days where any single transaction exceeded $1M”: HAVING MAX(total) > 1000000.

The pattern: any predicate that depends on a per-group summary lives in HAVING.

When WHERE is what you want

  • “Only April orders”: WHERE placed >= '2026-04-01' AND placed < '2026-05-01'.
  • “Exclude test users”: WHERE user_id NOT IN (1, 2, 3).
  • “Only paid orders”: WHERE status = 'paid'.

The pattern: any predicate that depends only on the row itself lives in WHERE.

When both are right

Often a single query has both. The earlier you can apply a filter, the faster the query — WHERE filters first, eliminating rows from the input to the (expensive) grouping step.

SELECT user_id, COUNT(*) AS order_count
FROM   orders
WHERE  placed >= '2026-04-01'    -- cheap, indexed
  AND  status  = 'paid'           -- cheap, partial-indexable
GROUP  BY user_id
HAVING COUNT(*) >= 5;             -- requires aggregation result

Mental check: can the predicate be evaluated against a single row in isolation? If yes, WHERE. If it needs the whole group, HAVING.

Subtle: HAVING without GROUP BY

You can have HAVING without GROUP BY. The whole result is treated as one group:

-- Returns one row only if there are at least 100 orders
SELECT COUNT(*) AS n
FROM   orders
HAVING COUNT(*) >= 100;

Rare in practice, useful for EXISTS-style guards in dashboards.

Common pitfalls

  • Putting an aggregate in WHERE. WHERE SUM(x) > 5 errors out; beginners then write a subquery to “fix” it instead of using HAVING.
  • Putting a row predicate in HAVING. It works (a row is a degenerate one-row group from the predicate’s perspective if you wrap it in MAX()/MIN()) but it skips index usage and reads the entire table. Always prefer WHERE when possible.
  • HAVING referencing an alias from SELECT. Standard SQL says no (the alias is created later). Postgres allows it; MySQL does too. Don’t rely on it for portability.
  • Filtering on a column not in GROUP BY or aggregate. HAVING country = 'US' after GROUP BY country is fine; after GROUP BY user_id is an error (which country is the group’s country?).

Production patterns for ML

1. Filtering for active users in feature pipelines. Two filters: events in window (WHERE), users with enough events to be modeled (HAVING):

SELECT user_id,
       COUNT(*)                          AS events,
       AVG(EXTRACT(EPOCH FROM duration)) AS avg_duration_s
FROM   sessions
WHERE  started_at >= CURRENT_DATE - INTERVAL '30 days'
GROUP  BY user_id
HAVING COUNT(*) >= 10;          -- drop sparse users

The 10-event minimum keeps the model from learning on near-empty histories.

2. Slice analysis after the fact. “Which segments have AUC < 0.7?”:

SELECT country, country_age_bucket,
       AVG(prediction = label::int)::float AS accuracy,
       COUNT(*) AS n
FROM   predictions
GROUP  BY country, country_age_bucket
HAVING COUNT(*) >= 100              -- enough samples to trust the metric
   AND AVG(prediction = label::int)::float < 0.70;

The COUNT(*) >= 100 filter excludes tiny groups whose accuracy is just noise.

Resources