Oracle show all employees with greater than average salary of their department

Question

I am writing a query to find employees who earn greater than the average salary within their department. I need to display the employee ID, salary, department id, and average salary of that department.

I have a query that just almost works but it keeps giving me "ORA-00904: "AVG_SAL": invalid identifier" errors. Am I doing this correctly. Why am i getting this invalid identifier error?

SELECT employee_id, salary, department_id,
  (SELECT ROUND(AVG(salary),2)
  FROM employees e_inner
  WHERE e_inner.department_id = e.department_id) AS avg_sal
FROM employees e
WHERE salary > avg_sal
ORDER BY avg_sal DESC

Answer 1

More efficient to use analytics:

select employee_id, salary, department_id, avg_sal
from
(
  SELECT employee_id, salary, department_id, 
    round(avg(salary) over (partition by department_id), 2) avg_sal
  from emp
)
where salary > avg_sal
order by avg_sal desc