title: Oracle show all employees with greater than average salary of their department
body: <p>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.</p>
  
  <p>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?</p>
  
  <pre><code>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 &gt; avg_sal
  ORDER BY avg_sal DESC
  </code></pre>