SQL WHERE vs. HAVING: Filter Paid Orders Before COUNT(*)

You want customers with at least two paid orders, but WHERE COUNT(*) >= 2 fails. Why can a condition not go in WHERE? Filtering individual rows and filtering groups after aggregation happen at different stages.

Why does COUNT in WHERE fail?

In the eight orders below, Ana has two paid orders, while Ben and Cora each have one. Cancelled orders make the total number of orders different from the paid-order count.

CREATE TABLE orders (
  order_id INTEGER PRIMARY KEY,
  customer TEXT NOT NULL,
  status TEXT NOT NULL
);

INSERT INTO orders (order_id, customer, status) VALUES
  (1, 'Ana', 'paid'), (2, 'Ana', 'paid'), (3, 'Ana', 'cancelled'),
  (4, 'Ben', 'paid'), (5, 'Ben', 'cancelled'),
  (6, 'Cora', 'paid'), (7, 'Cora', 'cancelled'), (8, 'Cora', 'cancelled');

SELECT customer, COUNT(*)
FROM orders
WHERE COUNT(*) >= 2
GROUP BY customer;

SQLite rejects the second query with misuse of aggregate: COUNT(). WHERE evaluates input order rows; COUNT(*) requires a group of rows. The query asks for a group count before forming the groups. SQLite's SELECT documentation describes the row-filtering and aggregation stages.

WHERE chooses which orders to count

To count paid orders only, filter status = 'paid' before grouping:

SELECT customer, COUNT(*) AS paid_count
FROM orders
WHERE status = 'paid'
GROUP BY customer
ORDER BY customer;

customer  paid_count
Ana       2
Ben       1
Cora      1

Four paid rows remain out of the original eight. Grouping by customer gives counts 2, 1, 1. COUNT(*) counts the remaining rows, not all eight original orders.

HAVING chooses which customer groups remain

Now retain only customer groups with at least two paid orders. That group-level condition belongs in HAVING after GROUP BY.

The diagram shows WHERE selecting paid orders and GROUP BY calculating each customer's count. HAVING then keeps only Ana, whose count is at least two.

SELECT customer, COUNT(*) AS paid_count
FROM orders
WHERE status = 'paid'
GROUP BY customer
HAVING COUNT(*) >= 2
ORDER BY customer;

customer  paid_count
Ana       2

WHERE filters order rows; HAVING filters customer groups. They are not two interchangeable names for the same operation.

Why do more customers appear without WHERE?

Remove WHERE status = 'paid' while leaving HAVING COUNT(*) >= 2, and Ana's three orders, Ben's two, and Cora's three all pass. That counts cancelled orders too. A query can run successfully while answering the wrong business question.

If an aggregate result looks wrong, first confirm which orders should be counted, then check the group threshold. If the service adds more statuses, define what “paid” means in its data contract. Whether refunded orders qualify is a business rule, not a SQL syntax rule.

Key takeaways

For customers with two or more paid orders, select rows with WHERE status = 'paid', group with GROUP BY customer, and retain groups with HAVING COUNT(*) >= 2. COUNT(*) does not belong in WHERE; omitting the row filter may count cancelled orders. Read the query as two questions: “What are we counting?” and “Which counts qualify?”