'Subquery with average

I am trying (sql server) to show the employees whose salaries are smaller than the average of the salaries in their profession

I am trying the next query but it appears: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

SELECT*
FROM EMPLOYEE
WHERE SALARY< (SELECT AVG(SALARY)
               FROM EMPLOYEE
               GROUP BY PROFESSION
               )

Can someone help me?

thanks

sqlsubquery


Solution 1:[1]

I would use AVG() as an analytic function here:

WITH cte AS (
    SELECT e.*, AVG(SALARY) OVER (PARTITION BY PROFESSION) AVG_SALARY
    FROM EMPLOYEE e
)

SELECT *
FROM cte
WHERE SALARY < AVG_SALARY;

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Tim Biegeleisen

Related Questions