3
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.

Itay Gurvich
  • 53
  • 7

2 Answers2

3

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)
rcs
  • 6,713
  • 12
  • 53
  • 75
0

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;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786