title: Correlated subquery to find employees earning more than average salary in their departments
body: <p>I am writing an Oracle query to find employees who earn more than the average salary within their departments. I need to display the last_name,employee_ID, salary, department_id and average salary of that department. I have written this query but I am not getting the desired answer. Can please someone tell me, where I am making the mistake</p>
  
  <pre><code>select last_name,employee_id,salary,department_id,avg(salary) 
    from employees e1
   where salary &gt; (select avg(salary) 
                     from employees e2 
                    where e1.department_id=e2.department_id)
   group by last_name,employee_id,salary,department_id
  </code></pre>