title: Employees with higher salary than their department average?
body: <p>i have a table called employees which i have name, department_id and salary in it. I want to find the employees whose salary is greater than the average of their department and see their names, department_id, salary and the average salary of their department. I have written this code but it does not work. How can we fix this? Thanks in advance.</p>
  
  <pre><code>    SELECT name, department_id, salary, avg(salary)
      FROM employees
      GROUP BY name, department_id, salary
      HAVING salary &gt; (select avg(salary) from employees group by department_id)
  </code></pre>
  
  <p>I have updated my code as you said like:</p>
  
  <pre><code>    SELECT department_id, salary, avg(salary), count(*)
      FROM employees e
      GROUP BY department_id, salary
      HAVING salary &gt; (select avg(salary) from employees e2 where e2.department_id=e.department_id)
  </code></pre>
  
  <p>But when i run this i get this result:</p>
  
  <p><img src="https://i.stack.imgur.com/BFkMM.png" alt="result"></p>
  
  <p>You can see that salary and the averages are the same and there are 2 department 80's, i need 1 of all the existing departments. How can we fix this. I am using the Oracle database if that's any important. Thanks.</p>