HAVING vs WHERE, and the order a query runs in

Part of the free LogicWiz SQL & Databases course, Module 4: Aggregates and GROUP BY. The queries and practice run in the app on real Postgres.

Meher wants a Diwali coupon for customers with 3 or more orders. She writes WHERE COUNT(*) >= 3 and Postgres says aggregates are not allowed in WHERE. The fix is one word, and the reason explains how every query runs.

You'll learn to

• Filter groups with HAVING and rows with WHERE
• Use both in one query
• Recite the logical order: FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY, LIMIT
• Know where an alias can and cannot be used

Learn

Two filters, two moments

WHERE looks at one row at a time, before any grouping. It can test status or ordered_at, but it cannot test COUNT(*), because no groups exist yet.

HAVING looks at one group at a time, after GROUP BY has built the buckets and the aggregates are known. It can test COUNT(*) >= 3.

Rule of thumb: if the condition has an aggregate in it, it goes in HAVING. Everything else goes in WHERE.

Customers with 3 or more orders

SELECT customer_id, COUNT(*) AS orders
FROM orders
GROUP BY customer_id
HAVING COUNT(*) >= 3
ORDER BY orders DESC, customer_id;

GROUP BY builds 10 customer groups. HAVING keeps the 4 groups whose count is at least 3.

WHERE and HAVING together

SELECT customer_id,
  COUNT(*) AS delivered_orders,
  SUM(delivery_fee) AS fees_paid
FROM orders
WHERE status = 'delivered'
GROUP BY customer_id
HAVING COUNT(*) >= 3
ORDER BY customer_id;

WHERE first throws away the cancelled and pending orders. Then the groups are counted, and HAVING keeps the big ones. Customer 2 had 3 orders, but only 2 were delivered, so they drop out.

The logical order of a query

You write SELECT first, but Postgres runs it fifth. This one table explains most GROUP BY errors.

Why an alias fails in WHERE

SELECT name, price * 1.05 AS with_gst
FROM menu_items
WHERE with_gst > 400;

with_gst is created at step 5 (SELECT), but WHERE runs at step 2, so it does not exist yet. Repeat the expression in WHERE. ORDER BY runs after SELECT, so ORDER BY with_gst works.

Do not move a plain row filter into HAVING. HAVING status = 'delivered' fails unless status is grouped, and even when it works it filters after the expensive grouping. Row conditions belong in WHERE; aggregate conditions in HAVING.

MySQL lets you use a SELECT alias in HAVING (HAVING orders >= 3). Postgres does not; write the aggregate again: HAVING COUNT(*) >= 3. The second form works in both, so prefer it.

"What is the difference between WHERE and HAVING?" and "In what order does SQL run the clauses?" are asked in almost every SQL interview and in university exams. Answer with the two moments (rows before grouping, groups after) and the 8-step order above.

In one line

WHERE filters rows before grouping; HAVING filters groups after; the logical order is FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY, LIMIT.