converting correlated oracle subquery to single query

Question

Hi I have two tables EMP and Dept( data as normal emp and dept tables of oracle) I want to find the number of employees earning more than the avg(sal) of their own dept without use of correlated subquery. I wrote the query as below

SELECT * from emp e JOIN
   (SELECT avg(sal) avgsal,deptno
          FROM emp group by deptno )avgsal_tab
 on e.sal > avgsal_tab.avgsal where e.deptno =avgsal_tab.deptno
 order by e.deptno 

This gets me the output but how can I rewrite this with a single query without inline query as shown above.

Answer 1

You can use analytic window functions:

SELECT *
from (
  SELECT 
    e.*
    ,avg(sal)over(partition by deptno) avgsal
  from emp e
)
where avgsal>sal