How to select employees with salary less then the average of their departament using SQL analytical functions?

Question

I'm trying to make a select statement on the employees who have their salary less than the average of the department they work in using SQL analytical functions. The code above shows me all the employees and the average of their department. How can I restrict it so it only selects the employees with their salary < department average?

SELECT NAME, SALARY, ID_DEP, AVG(SALARY) OVER (PARTITION BY ID_DEP) DEP_AVERAGE FROM EMPLOYEES;

Answer 1

Use a window function and a subquery:

select e.*
from (select e.*, 
             avg(salary) over (partition by id_dep) as avg_salary
      from employees e
     ) e
where salary < avg_salary;

You cannot use window functions in where or having clauses. Some databases support a qualify clause that filters on window functions; but Oracle does not.