SQL WHERE AND error message

Vote count: 0

SELECT *
FROM dbo.staff
WHERE st_position = 'Supervisor' AND st_salary < AVG(st_salary);

So I'm trying to set a query that outputs a list of all supervisors that have a salary lower than average. putting this in I get the following error.

Msg 147, Level 15, State 1, Line 1 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.

asked 1 min ago

Itay Gurvich

1 Answer

Vote count: 0

You should use HAVING clause

SELECT *
FROM dbo.staff
WHERE st_position = 'Supervisor' 
HAVING st_salary < AVG(st_salary);

answered 20 secs ago

rcs

Let's block ads! (Why?)

SQL WHERE AND error message