Caso Abierto Español

Field manual · SQL traps

Why doesn't WHERE find the customer who 'always' pays cash?

Filtering in the WHERE before grouping answers a different question: it counts who paid cash several times, it doesn't check they ALWAYS did. 'Always' describes the whole group, and that belongs in HAVING.

Where it shows up

Any question with 'always', 'never' or 'every time': the whole group needs to reach GROUP BY before you decide whether it follows the rule.

Example

Wrong: the WHERE already threw away the counter-evidence

SELECT customer
FROM payments
WHERE method = 'cash'
GROUP BY customer
HAVING count(*) >= 3;
-- this finds anyone who paid cash 3 times or more,
-- even if they also paid by card: the WHERE already dropped that row

Right: group everything, check 'always' in the HAVING

SELECT customer
FROM payments
GROUP BY customer
HAVING count(*) >= 3
   AND count(*) FILTER (WHERE method <> 'cash') = 0;

WHERE method = 'cash' runs before GROUP BY: it drops the card rows before the group even forms, so HAVING count(*) >= 3 only sees the cash rows that survived and never finds out that customer also paid by card at some point. What that query really answers is 'did they pay cash at least three times', not 'did they always pay cash'. To ask about the whole group, the whole group needs to reach GROUP BY: drop the WHERE and use count(*) FILTER (WHERE method <> 'cash') = 0 to require that no row breaks the rule, together with count(*) >= 3 to require a minimum amount of data. FILTER counts conditionally without needing to delete rows before the group is complete.