Skip to main content
CodeSparring

HAVING: Filtering Groups

Level 2: Level 2: Aggregation & Joins (Combining Source Data)medium25 minHAVINGWHERE vs HAVINGfiltering on aggregates

Filter on aggregated values, not raw rows.

Filter groups, not rows

You know how to compute revenue per category. Now the analyst says: "only show me categories that did more than $1,000." You can't put that in WHERE. At the time WHERE runs, there is no per-category total yet; WHERE sees raw rows, one at a time. You need a filter that runs after grouping, on the aggregated value. That's HAVING.

The pipeline order is the whole lesson:

Order of evaluation
  1. FROM / JOINassemble the rows
  2. WHEREkeep rows that pass
  3. GROUP BYcollapse into groups
  4. HAVINGkeep groups that pass
  5. SELECTproject columns + window fns
  6. ORDER BYsort the result
WHERE filters raw rows before grouping; HAVING filters whole groups by their aggregate after grouping. Same pipeline, two different phases.

Worked example, categories whose total revenue clears a threshold:

SELECT
  category,
  SUM(line_revenue_cents) AS revenue_cents
FROM order_items_wide
GROUP BY category
HAVING SUM(line_revenue_cents) > 100000;   -- filters GROUPS, not rows

Anatomy: WHERE vs HAVING

WHERE  status = 'paid'                 → keeps rows where a raw column matches
HAVING SUM(revenue_cents) > 100000     → keeps groups where an aggregate matches

The two are not interchangeable, and using the wrong one changes the answer, not just performance. Consider "categories where paid revenue exceeds $1,000":

  • Correct: WHERE status='paid' (drop unpaid rows) → GROUP BY category → HAVING SUM(revenue) > 100000.
  • Wrong: putting the status test in HAVING, or the revenue test in WHERE. WHERE SUM(...) > 100000 is a hard error: you cannot aggregate in WHERE.

Keep it readable / common pitfall

Use WHERE for everything you can: filtering rows early shrinks the data before the expensive grouping, and it's cheaper in every engine. Reserve HAVING strictly for conditions that reference an aggregate. A HAVING category = 'audio' (no aggregate) works in SQLite but belongs in WHERE; it signals you've confused the two phases.

In the warehouse this differs. Every engine (Postgres, Snowflake, BigQuery) runs this same logical pipeline (WHERE before grouping, HAVING after), so the mental model ports unchanged. An optimizer may physically reorder or push down work, but it can never let a HAVING aggregate be evaluated before its group exists, which is exactly why an aggregate test cannot live in WHERE.

Recap. WHERE filters raw rows before grouping; HAVING filters whole groups by their aggregate after grouping. Put every non-aggregate condition in WHERE and reserve HAVING for tests on SUM/COUNT/AVG.

Sample data for this example
CREATE TABLE order_items_wide (
  order_item_id      INTEGER PRIMARY KEY,
  category           TEXT,
  line_revenue_cents INTEGER
);
INSERT INTO order_items_wide VALUES
  (1, 'audio',     60000),
  (2, 'audio',     75000),
  (3, 'wearables', 90000),
  (4, 'wearables', 65000),
  (5, 'cables',     5000),
  (6, 'cables',     3000);
Worked example (SQL)
SELECT
  category,
  SUM(line_revenue_cents) AS revenue_cents
FROM order_items_wide
GROUP BY category
HAVING SUM(line_revenue_cents) > 100000;

Apply

Your turn

The task this lesson builds to.

From order_items_wide, keep only categories whose total revenue exceeds 5000 cents. Return category and revenue_cents (the summed line_revenue_cents), sorted by category ascending.

3 hints and 1 automated check are waiting in the workspace.

Practice

Make it stick

A second problem on the same idea, plus 3 bonus drills.

Flag high-value segments. From orders, find customers who are BOTH frequent AND high-spend. Consider only paid orders (a pre-aggregation filter). Group by customer_id and keep customers with more than 3 paid orders AND lifetime paid revenue over 20000 cents. Return customer_id, order_count (count of their paid orders), and lifetime_revenue (sum of their paid total_cents), sorted by lifetime_revenue descending. Combine the pre-aggregation WHERE status='paid' with a two-condition HAVING.

4 hints and 1 automated check are waiting in the workspace.