Can you use an aggregate function in a WHERE clause?
SQLLevel: FoundationAggregations Basics
Can you use an aggregate function in a WHERE clause?
Hints
- Define one output row and the source-table keys.
- Check duplicates, NULLs and the order of filtering and aggregation.
Answer
No. Aggregate functions run after WHERE (in the GROUP BY / HAVING phase). If you try to put an aggregate in WHERE, you'll get an error.
-- This FAILS
SELECT department, COUNT(*) FROM employees
WHERE COUNT(*) > 5 -- Error
GROUP BY department;
-- This works
SELECT department, COUNT(*) FROM employees
GROUP BY department
HAVING COUNT(*) > 5;
Source and licence: SQL Interview Questions — Arda Kocadoru · MIT