Why can't I put COUNT(*) > 3 in WHERE?

WHERE runs before groups exist. There is no COUNT(*) yet. Postgres and SQL Server error. MySQL may error or surprise you depending on ONLY_FULL_GROUP_BY. The threshold on an aggregate belongs in HAVING.

So WHEN do I still use WHERE?

WHERE filters raw rows (this year, not deleted, one tenant). HAVING filters groups after aggregation (more than 3 orders, total over $500). You usually want both: WHERE for the period, HAVING for the threshold.

Is this the same as AND/OR returning too many rows?

Same smell (the list is too big), different mechanism. AND/OR is precedence. This is clause timing: the count filter never ran, so every customer with at least one matching row survives.

What if the AI used a subquery instead of HAVING?

That's valid — filter COUNT in an inner query, then WHERE on the outer. HAVING is just the direct form. Either is fine if the threshold is applied after the group, not before.

How do I catch it without reading SQL?

Ask for the count of groups that pass the threshold and a sample customer you know has 1 order vs 10. If the 1-order customer is in the list, the threshold never ran.

HAVING vs WHERE SQL | Filter After GROUP BY

You ask for "customers with more than three orders this year." You get back almost every customer who ordered at all — or a syntax error about aggregates in WHERE. The AI grouped correctly, then put COUNT(*) > 3 in the wrong clause.

WHERE is too early

-- errors (or is meaningless): COUNT does not exist yet
SELECT customer_id, COUNT(*) AS orders
FROM orders
WHERE created_at >= '2026-01-01'
  AND COUNT(*) > 3          -- too soon
GROUP BY customer_id

WHERE filters rows before GROUP BY. A row does not have a COUNT(*). The database either rejects the query or the threshold is ignored, and you get one row per customer who had any order in the filter — which looks like "everyone."

-- what you meant
SELECT customer_id, COUNT(*) AS orders
FROM orders
WHERE created_at >= '2026-01-01'
GROUP BY customer_id
HAVING COUNT(*) > 3

HAVING runs after the group exists. That's when "more than three" is a real number.

This is the opposite timing bug from LEFT JOIN + WHERE (filter too late, drops unmatched rows). Here the filter is too early, so the threshold never applies.

Both clauses at once

Most real questions need both:

WHERE — this year, this tenant, not refunded, deleted_at IS NULL
HAVING — at least N orders, sum over $X, more than one plan

If the AI only writes WHERE, you get a pile of groups. If it only writes HAVING and skips the date filter, last year's orders inflate the count — a cousin of metric definition traps.

How to catch it

Ask for a customer you know has one order this year and one who has ten. If both appear in "more than three," the HAVING is missing. Also ask: "show the SQL — is the threshold in HAVING or WHERE?"

The prompt that heads this off

Filters on raw columns (dates, status, tenant) go in WHERE.
Filters on COUNT / SUM / AVG after GROUP BY go in HAVING.
Don't put COUNT(*) in WHERE. If I say "with more than N," that's HAVING.

The short version

"More than three orders" is a fact about a group, not a row. WHERE never sees groups. If the list looks like "anyone who ordered," the threshold is in the wrong clause.

Read-only MCP gateway: mcpserver.design.

Related reading: