Search code examples
sqlwhere-clausepostgresqlstudio

SQL WHERE AND error message

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.

Solution

  • 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)