HAVING Versus WHERE

SQLAggregationJuniorMidUpdated Nov 10, 2026

Return each customer_id with more than 5 orders, along with their order count.

Schema

CREATE TABLE orders (
  id           BIGINT PRIMARY KEY,
  customer_id  BIGINT NOT NULL,
  total        NUMERIC NOT NULL
);

Drill This: Your Query First

Answer, Then Reveal

Write the query before you look at the answer.

Skip to Reading

The Correct Query

Option (b)

SELECT customer_id, COUNT(*) AS order_count
FROM orders
GROUP BY customer_id
HAVING COUNT(*) > 5;

WHERE filters rows before grouping, so it cannot see an aggregate: (a) fails with 'aggregate functions are not allowed in WHERE'. HAVING filters groups after GROUP BY, which is where COUNT(*) exists. (c) is a MySQL habit: Postgres does not let HAVING refer to a SELECT alias, so it fails with column "order_count" does not exist. (d) returns the top 5 customers, which answers a different question.

Going Deeper

The logical order of a query explains most of these errors: FROM and JOIN, then WHERE, then GROUP BY, then HAVING, then SELECT, then ORDER BY and LIMIT. ORDER BY can use a SELECT alias because it runs after SELECT; WHERE and HAVING run before it.

The Options That Fail

The explanation above says why each of these breaks.

Option (a)

SELECT customer_id, COUNT(*) AS order_count
FROM orders
WHERE COUNT(*) > 5
GROUP BY customer_id;

Option (c)

SELECT customer_id, COUNT(*) AS order_count
FROM orders
GROUP BY customer_id
HAVING order_count > 5;

Option (d)

SELECT customer_id, COUNT(*) AS order_count
FROM orders
GROUP BY customer_id
ORDER BY order_count DESC
LIMIT 5;