SQL WHERE AND error message

105 Views Asked by At
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.

2

There are 2 best solutions below

1
On BEST ANSWER

To get the value of average salary, we can use the following query:

SELECT AVG(st_salary) FROM dbo.staff

Combining it together with the other condition, will give the following query:

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

I would do this using window functions:

SELECT s.*
FROM (SELECT s.*, AVG(st_salary) OVER () as avg_st_slary
      FROM dbo.staff s
      WHERE s.st_position = 'Supervisor'
     ) s 
WHERE st_salary < avg_st_salary;