Skip to content
Quantoros

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

Related questions