SQL practice › SQL errors › Aggregate function in WHERE

Aggregate functions are not allowed in WHERE: use HAVING or a subquery

WHERE decides, one row at a time, which rows go into the query. COUNT, SUM, AVG, MIN and MAX are computed afterwards, from the rows that got through. So a WHERE can't contain one of them: the number it asks for doesn't exist yet.

There are two fixes, for two different intentions. If the condition is about a group ("departments with more than two people"), move it to HAVING, which runs after GROUP BY. If it compares each row with a total ("employees who earn more than the average"), compute the total in a subquery. Using the wrong one of the two gives a query that runs and returns nothing, which is shown below.

Skip to the 4 exercises ↓

The message in each database

The table

Nine employees: four in Engineering, three in Sales, two in Support.

employees
idnamedepartmentsalarymanager_idhire_date
1MayaSales72000NULL2019-03-11
2OmarSales5800012021-06-01
3LenaSales5800012022-02-14
4RajEngineering95000NULL2018-09-03
5ZoeEngineering8800042020-01-20
6IvanEngineering8100042021-11-08
7NoraSupport46000NULL2020-07-15
8TheoSupport4300072023-04-03
9KimEngineering8800042022-08-22

A condition on a group: HAVING

"Departments with more than two people" is a statement about a department, not about any single employee. It can only be tested once the rows are grouped and counted.

Live queryCOUNT in WHERE
Table: employees
employees
idnamedepartmentsalarymanager_idhire_date
1MayaSales72000NULL2019-03-11
2OmarSales5800012021-06-01
3LenaSales5800012022-02-14
4RajEngineering95000NULL2018-09-03
5ZoeEngineering8800042020-01-20
6IvanEngineering8100042021-11-08
7NoraSupport46000NULL2020-07-15
8TheoSupport4300072023-04-03
9KimEngineering8800042022-08-22
Ctrl/⌘ + Enter to run

SQLite says: misuse of aggregate: COUNT()

The two clauses are not rivals. A query can have both, each at its own stage: WHERE chooses the rows, GROUP BY folds them, HAVING chooses the groups.

Live queryWHERE and HAVING in the same query
Table: employees
employees
idnamedepartmentsalarymanager_idhire_date
1MayaSales72000NULL2019-03-11
2OmarSales5800012021-06-01
3LenaSales5800012022-02-14
4RajEngineering95000NULL2018-09-03
5ZoeEngineering8800042020-01-20
6IvanEngineering8100042021-11-08
7NoraSupport46000NULL2020-07-15
8TheoSupport4300072023-04-03
9KimEngineering8800042022-08-22
Ctrl/⌘ + Enter to run
Result · 1 row
departmentCOUNT(*)
Engineering4

A row compared with a total: a subquery

"Employees who earn more than the average" is a statement about each employee, so it does belong in WHERE. What can't be there is the bare AVG.

Live queryAVG in WHERE
Table: employees
employees
idnamedepartmentsalarymanager_idhire_date
1MayaSales72000NULL2019-03-11
2OmarSales5800012021-06-01
3LenaSales5800012022-02-14
4RajEngineering95000NULL2018-09-03
5ZoeEngineering8800042020-01-20
6IvanEngineering8100042021-11-08
7NoraSupport46000NULL2020-07-15
8TheoSupport4300072023-04-03
9KimEngineering8800042022-08-22
Ctrl/⌘ + Enter to run

SQLite says: misuse of aggregate function AVG()

The wrong fix. After reading "use HAVING", it is tempting to move this condition to HAVING too. HAVING needs groups, so a GROUP BY name gets added to make the query run. It runs.

Live queryMoved to HAVING: no error, no rows
Table: employees
employees
idnamedepartmentsalarymanager_idhire_date
1MayaSales72000NULL2019-03-11
2OmarSales5800012021-06-01
3LenaSales5800012022-02-14
4RajEngineering95000NULL2018-09-03
5ZoeEngineering8800042020-01-20
6IvanEngineering8100042021-11-08
7NoraSupport46000NULL2020-07-15
8TheoSupport4300072023-04-03
9KimEngineering8800042022-08-22
Ctrl/⌘ + Enter to run
Result · 0 rows
No rows

An alias doesn't get around it

Naming the aggregate in the SELECT list and using the name in WHERE looks like a way out. The name still stands for the aggregate.

Live queryThe alias of a COUNT in WHERE
Table: employees
employees
idnamedepartmentsalarymanager_idhire_date
1MayaSales72000NULL2019-03-11
2OmarSales5800012021-06-01
3LenaSales5800012022-02-14
4RajEngineering95000NULL2018-09-03
5ZoeEngineering8800042020-01-20
6IvanEngineering8100042021-11-08
7NoraSupport46000NULL2020-07-15
8TheoSupport4300072023-04-03
9KimEngineering8800042022-08-22
Ctrl/⌘ + Enter to run

SQLite says: misuse of aggregate: COUNT()

Practice: 4 exercises

Write the query in the box and press Run. Your result is compared with the right one, on this table and again on changed data, so a query with the values typed in won't pass. For each task, decide first: is the condition about a group, or about a row?

Exercise 1Departments with a large salary bill

Return each department whose salaries add up to more than 150000, with that total.

Table: employees
employees
idnamedepartmentsalarymanager_idhire_date
1MayaSales72000NULL2019-03-11
2OmarSales5800012021-06-01
3LenaSales5800012022-02-14
4RajEngineering95000NULL2018-09-03
5ZoeEngineering8800042020-01-20
6IvanEngineering8100042021-11-08
7NoraSupport46000NULL2020-07-15
8TheoSupport4300072023-04-03
9KimEngineering8800042022-08-22
Ctrl/⌘ + Enter to run
Show the answer
SELECT department, SUM(salary)
FROM employees
GROUP BY department
HAVING SUM(salary) > 150000
Result · 2 rows
departmentSUM(salary)
Engineering352000
Sales188000

The condition is about a department's total, which exists only after GROUP BY, so it goes in HAVING. Engineering (352000) and Sales (188000) pass. Support's total is below 150000.

Exercise 2Who was hired first

Return the name and hire date of the employee who was hired first. If several people share that date, return all of them.

Table: employees
employees
idnamedepartmentsalarymanager_idhire_date
1MayaSales72000NULL2019-03-11
2OmarSales5800012021-06-01
3LenaSales5800012022-02-14
4RajEngineering95000NULL2018-09-03
5ZoeEngineering8800042020-01-20
6IvanEngineering8100042021-11-08
7NoraSupport46000NULL2020-07-15
8TheoSupport4300072023-04-03
9KimEngineering8800042022-08-22
Ctrl/⌘ + Enter to run
Show the answer
SELECT name, hire_date
FROM employees
WHERE hire_date = (SELECT MIN(hire_date) FROM employees)
Result · 1 row
namehire_date
Raj2018-09-03

The condition is about each employee (was this person hired on the earliest date?), so it stays in WHERE, and the earliest date comes from a subquery. Raj, hired in 2018, is the only one here. ORDER BY hire_date LIMIT 1 would return one row even if two people had started that day.

Exercise 3A filter on rows and a filter on groups

Count only the employees hired on or after 2021-01-01. Return each department that has at least two of them, with how many it has.

Table: employees
employees
idnamedepartmentsalarymanager_idhire_date
1MayaSales72000NULL2019-03-11
2OmarSales5800012021-06-01
3LenaSales5800012022-02-14
4RajEngineering95000NULL2018-09-03
5ZoeEngineering8800042020-01-20
6IvanEngineering8100042021-11-08
7NoraSupport46000NULL2020-07-15
8TheoSupport4300072023-04-03
9KimEngineering8800042022-08-22
Ctrl/⌘ + Enter to run
Show the answer
SELECT department, COUNT(*)
FROM employees
WHERE hire_date >= '2021-01-01'
GROUP BY department
HAVING COUNT(*) >= 2
Result · 2 rows
departmentCOUNT(*)
Engineering2
Sales2

The date condition is about single employees, so it goes in WHERE and is applied before anything is counted. "At least two" is about the group, so it goes in HAVING. Sales and Engineering each have two people hired since 2021; Support has one and drops out.

Exercise 4Departments above the company average

Return each department whose average salary is higher than the average salary of the whole company, with the department's average salary.

Table: employees
employees
idnamedepartmentsalarymanager_idhire_date
1MayaSales72000NULL2019-03-11
2OmarSales5800012021-06-01
3LenaSales5800012022-02-14
4RajEngineering95000NULL2018-09-03
5ZoeEngineering8800042020-01-20
6IvanEngineering8100042021-11-08
7NoraSupport46000NULL2020-07-15
8TheoSupport4300072023-04-03
9KimEngineering8800042022-08-22
Ctrl/⌘ + Enter to run
Show the answer
SELECT department, AVG(salary)
FROM employees
GROUP BY department
HAVING AVG(salary) > (SELECT AVG(salary) FROM employees)
Result · 1 row
departmentAVG(salary)
Engineering88000

Both tools at once. The department's average is a fact about a group, so the condition goes in HAVING. The company's average is a number from outside the group, so it comes from a subquery. Only Engineering, at 88000, is above it.

Questions people ask

Why can't I use COUNT or AVG in WHERE?

Because of the order in which a query is worked out: WHERE first filters single rows, then GROUP BY forms the groups, then the aggregates are computed. An aggregate in WHERE would need its own result to decide which rows it is computed from.

What is the difference between WHERE and HAVING?

WHERE filters rows before grouping; HAVING filters groups after it. A condition on a plain column (salary > 60000) goes in WHERE. A condition on an aggregate (COUNT(*) > 2) goes in HAVING. A query can have both, as the second live query shows.

When do I need a subquery instead of HAVING?

When you compare each row with a number computed from many rows: "earns more than the average", "was hired on the earliest date". The rows are not being grouped, so there is nothing for HAVING to filter. Write the aggregate as a query of its own in parentheses, and compare with that.

Can I put a condition without an aggregate in HAVING?

PostgreSQL's manual says it is allowed but seldom useful: the same condition is more efficient in WHERE, because the rows are discarded before the grouping and the aggregate calculations instead of after. Keep conditions on plain columns in WHERE.

Does naming the aggregate with AS let me use it in WHERE?

No. The alias is a name for the aggregate, and the aggregate still doesn't exist when WHERE runs. On SQLite the error is the same, as the last live query shows. Use HAVING.

Keep going

Lesson 2 is seven drills on COUNT, SUM, AVG, GROUP BY and HAVING on a table of orders, ending with the customers who have at least two orders. Lesson 4 is seven drills on subqueries and CTEs. Both are free, like lessons 1 and 3 and the level test.

Related

SQL WHERE vs HAVING, with live queriesSQL GROUP BY, with live queriesSQL subquery vs CTE: the same question three waysSQL GROUP BY practiceSQL subquery practiceSQL skills testSQL errors, with a query to fixSQL practice online

Written and tested by Nico, who builds SheetDrill (about). Every query on this page was run on SQLite, and every exercise checked against right and wrong answers, before the page was published. Other databases differ in places; where they do, the page says so. Checked against: PostgreSQL documentation: Aggregate Functions (tutorial), PostgreSQL documentation: Value Expressions (aggregate expressions), Oracle Database error messages: ORA-00934, SQL Server documentation: Database Engine events and errors (0 to 999). Last checked November 2026.