Employees With Higher Salary Than Their Department Average?

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

Solution 1:

Your code is quite close. But, instead of a group by in the subquery, it needs to be correlated to the outer query. And, you don't need an outer aggregation, just a where clause:

SELECT name, department_id, salary
FROM employees e
WHERE salary > (selectavg(salary) from employees e2 where e2.department_id = e.department_id);

However, you are presumably learning Oracle. You can also write this query using analytic functions:

select e.*from (select e.*, avg(salary) over (partitionby department) as avgsalary
      from employees e
     ) e
where e.salary > e.avgsalary;

Although this is probably a bit advanced for what you are learning, I encourage you to understand both queries.