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.
The message in each database
- SQLite (the database on this page):
misuse of aggregate, followed by the function's name, such asmisuse of aggregate function AVG(). - SQL Server: error 147,
An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference. - Oracle:
ORA-00934: group function is not allowed here. Oracle's description names both fixes: a subquery or aHAVINGclause. - PostgreSQL: its manual gives the rule and the reason. An aggregate may appear only in the SELECT list or in
HAVING, becauseWHEREis evaluated before the aggregates are computed.
The table
Nine employees: four in Engineering, three in Sales, two in Support.
| id | name | department | salary | manager_id | hire_date |
|---|---|---|---|---|---|
| 1 | Maya | Sales | 72000 | NULL | 2019-03-11 |
| 2 | Omar | Sales | 58000 | 1 | 2021-06-01 |
| 3 | Lena | Sales | 58000 | 1 | 2022-02-14 |
| 4 | Raj | Engineering | 95000 | NULL | 2018-09-03 |
| 5 | Zoe | Engineering | 88000 | 4 | 2020-01-20 |
| 6 | Ivan | Engineering | 81000 | 4 | 2021-11-08 |
| 7 | Nora | Support | 46000 | NULL | 2020-07-15 |
| 8 | Theo | Support | 43000 | 7 | 2023-04-03 |
| 9 | Kim | Engineering | 88000 | 4 | 2022-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.
Table: employees
| id | name | department | salary | manager_id | hire_date |
|---|---|---|---|---|---|
| 1 | Maya | Sales | 72000 | NULL | 2019-03-11 |
| 2 | Omar | Sales | 58000 | 1 | 2021-06-01 |
| 3 | Lena | Sales | 58000 | 1 | 2022-02-14 |
| 4 | Raj | Engineering | 95000 | NULL | 2018-09-03 |
| 5 | Zoe | Engineering | 88000 | 4 | 2020-01-20 |
| 6 | Ivan | Engineering | 81000 | 4 | 2021-11-08 |
| 7 | Nora | Support | 46000 | NULL | 2020-07-15 |
| 8 | Theo | Support | 43000 | 7 | 2023-04-03 |
| 9 | Kim | Engineering | 88000 | 4 | 2022-08-22 |
SQLite says: misuse of aggregate: COUNT()
WHERE runs first, on single rows, before any group exists: there is nothing to count yet. Delete the WHERE line and add HAVING COUNT(*) > 2 as the last line, after GROUP BY. Support, with its two people, is the department that goes.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.
Table: employees
| id | name | department | salary | manager_id | hire_date |
|---|---|---|---|---|---|
| 1 | Maya | Sales | 72000 | NULL | 2019-03-11 |
| 2 | Omar | Sales | 58000 | 1 | 2021-06-01 |
| 3 | Lena | Sales | 58000 | 1 | 2022-02-14 |
| 4 | Raj | Engineering | 95000 | NULL | 2018-09-03 |
| 5 | Zoe | Engineering | 88000 | 4 | 2020-01-20 |
| 6 | Ivan | Engineering | 81000 | 4 | 2021-11-08 |
| 7 | Nora | Support | 46000 | NULL | 2020-07-15 |
| 8 | Theo | Support | 43000 | 7 | 2023-04-03 |
| 9 | Kim | Engineering | 88000 | 4 | 2022-08-22 |
| department | COUNT(*) |
|---|---|
| Engineering | 4 |
WHERE first keeps the people who earn more than 60000, then the groups are counted, then HAVING keeps the groups with more than two of those people. Sales has three employees but only one above 60000, so it is gone.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.
Table: employees
| id | name | department | salary | manager_id | hire_date |
|---|---|---|---|---|---|
| 1 | Maya | Sales | 72000 | NULL | 2019-03-11 |
| 2 | Omar | Sales | 58000 | 1 | 2021-06-01 |
| 3 | Lena | Sales | 58000 | 1 | 2022-02-14 |
| 4 | Raj | Engineering | 95000 | NULL | 2018-09-03 |
| 5 | Zoe | Engineering | 88000 | 4 | 2020-01-20 |
| 6 | Ivan | Engineering | 81000 | 4 | 2021-11-08 |
| 7 | Nora | Support | 46000 | NULL | 2020-07-15 |
| 8 | Theo | Support | 43000 | 7 | 2023-04-03 |
| 9 | Kim | Engineering | 88000 | 4 | 2022-08-22 |
SQLite says: misuse of aggregate function AVG()
WHERE is looking at one row. Put the average in a query of its own, in parentheses: WHERE salary > (SELECT AVG(salary) FROM employees). The subquery runs on the whole table, returns one number, and each row is then compared with that number.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.
Table: employees
| id | name | department | salary | manager_id | hire_date |
|---|---|---|---|---|---|
| 1 | Maya | Sales | 72000 | NULL | 2019-03-11 |
| 2 | Omar | Sales | 58000 | 1 | 2021-06-01 |
| 3 | Lena | Sales | 58000 | 1 | 2022-02-14 |
| 4 | Raj | Engineering | 95000 | NULL | 2018-09-03 |
| 5 | Zoe | Engineering | 88000 | 4 | 2020-01-20 |
| 6 | Ivan | Engineering | 81000 | 4 | 2021-11-08 |
| 7 | Nora | Support | 46000 | NULL | 2020-07-15 |
| 8 | Theo | Support | 43000 | 7 | 2023-04-03 |
| 9 | Kim | Engineering | 88000 | 4 | 2022-08-22 |
| No rows |
GROUP BY name makes one group per person, and inside a group of one, the average salary is that person's own salary. Nobody earns more than themselves. HAVING compares a group with its own aggregate; to compare a row with the whole table you need the subquery.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.
Table: employees
| id | name | department | salary | manager_id | hire_date |
|---|---|---|---|---|---|
| 1 | Maya | Sales | 72000 | NULL | 2019-03-11 |
| 2 | Omar | Sales | 58000 | 1 | 2021-06-01 |
| 3 | Lena | Sales | 58000 | 1 | 2022-02-14 |
| 4 | Raj | Engineering | 95000 | NULL | 2018-09-03 |
| 5 | Zoe | Engineering | 88000 | 4 | 2020-01-20 |
| 6 | Ivan | Engineering | 81000 | 4 | 2021-11-08 |
| 7 | Nora | Support | 46000 | NULL | 2020-07-15 |
| 8 | Theo | Support | 43000 | 7 | 2023-04-03 |
| 9 | Kim | Engineering | 88000 | 4 | 2022-08-22 |
SQLite says: misuse of aggregate: COUNT()
people as the COUNT(*) it names, and the error is the same. Move the condition to HAVING: HAVING COUNT(*) > 2.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?
Return each department whose salaries add up to more than 150000, with that total.
Table: employees
| id | name | department | salary | manager_id | hire_date |
|---|---|---|---|---|---|
| 1 | Maya | Sales | 72000 | NULL | 2019-03-11 |
| 2 | Omar | Sales | 58000 | 1 | 2021-06-01 |
| 3 | Lena | Sales | 58000 | 1 | 2022-02-14 |
| 4 | Raj | Engineering | 95000 | NULL | 2018-09-03 |
| 5 | Zoe | Engineering | 88000 | 4 | 2020-01-20 |
| 6 | Ivan | Engineering | 81000 | 4 | 2021-11-08 |
| 7 | Nora | Support | 46000 | NULL | 2020-07-15 |
| 8 | Theo | Support | 43000 | 7 | 2023-04-03 |
| 9 | Kim | Engineering | 88000 | 4 | 2022-08-22 |
Show the answer
SELECT department, SUM(salary)
FROM employees
GROUP BY department
HAVING SUM(salary) > 150000| department | SUM(salary) |
|---|---|
| Engineering | 352000 |
| Sales | 188000 |
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.
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
| id | name | department | salary | manager_id | hire_date |
|---|---|---|---|---|---|
| 1 | Maya | Sales | 72000 | NULL | 2019-03-11 |
| 2 | Omar | Sales | 58000 | 1 | 2021-06-01 |
| 3 | Lena | Sales | 58000 | 1 | 2022-02-14 |
| 4 | Raj | Engineering | 95000 | NULL | 2018-09-03 |
| 5 | Zoe | Engineering | 88000 | 4 | 2020-01-20 |
| 6 | Ivan | Engineering | 81000 | 4 | 2021-11-08 |
| 7 | Nora | Support | 46000 | NULL | 2020-07-15 |
| 8 | Theo | Support | 43000 | 7 | 2023-04-03 |
| 9 | Kim | Engineering | 88000 | 4 | 2022-08-22 |
Show the answer
SELECT name, hire_date
FROM employees
WHERE hire_date = (SELECT MIN(hire_date) FROM employees)| name | hire_date |
|---|---|
| Raj | 2018-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.
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
| id | name | department | salary | manager_id | hire_date |
|---|---|---|---|---|---|
| 1 | Maya | Sales | 72000 | NULL | 2019-03-11 |
| 2 | Omar | Sales | 58000 | 1 | 2021-06-01 |
| 3 | Lena | Sales | 58000 | 1 | 2022-02-14 |
| 4 | Raj | Engineering | 95000 | NULL | 2018-09-03 |
| 5 | Zoe | Engineering | 88000 | 4 | 2020-01-20 |
| 6 | Ivan | Engineering | 81000 | 4 | 2021-11-08 |
| 7 | Nora | Support | 46000 | NULL | 2020-07-15 |
| 8 | Theo | Support | 43000 | 7 | 2023-04-03 |
| 9 | Kim | Engineering | 88000 | 4 | 2022-08-22 |
Show the answer
SELECT department, COUNT(*)
FROM employees
WHERE hire_date >= '2021-01-01'
GROUP BY department
HAVING COUNT(*) >= 2| department | COUNT(*) |
|---|---|
| Engineering | 2 |
| Sales | 2 |
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.
Return each department whose average salary is higher than the average salary of the whole company, with the department's average salary.
Table: employees
| id | name | department | salary | manager_id | hire_date |
|---|---|---|---|---|---|
| 1 | Maya | Sales | 72000 | NULL | 2019-03-11 |
| 2 | Omar | Sales | 58000 | 1 | 2021-06-01 |
| 3 | Lena | Sales | 58000 | 1 | 2022-02-14 |
| 4 | Raj | Engineering | 95000 | NULL | 2018-09-03 |
| 5 | Zoe | Engineering | 88000 | 4 | 2020-01-20 |
| 6 | Ivan | Engineering | 81000 | 4 | 2021-11-08 |
| 7 | Nora | Support | 46000 | NULL | 2020-07-15 |
| 8 | Theo | Support | 43000 | 7 | 2023-04-03 |
| 9 | Kim | Engineering | 88000 | 4 | 2022-08-22 |
Show the answer
SELECT department, AVG(salary)
FROM employees
GROUP BY department
HAVING AVG(salary) > (SELECT AVG(salary) FROM employees)| department | AVG(salary) |
|---|---|
| Engineering | 88000 |
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.