Analytical Functions

Analytical Functions

 --by Raj
 

This type of SQL functions was introduced at oracle 8i release, One intresting fact is that most of the developers was not knowing it.


Example .

select deptno,avg(sal) as avg_sal from emp
group by deptno;

Above sql gives you average salary for each department.

If you need an out put for listing all the employees with department , salary and average salary of each employees department !!

a work around like below was mostly followed by most of the developers.

SELECT a.empno, a.deptno, a.sal,b.asl
FROM
emp a , (select deptno,avg(sal)asl from emp group by deptno )b
where
a.deptno = b.deptno;
SELECTa.empno, a.deptno, a.sal,b.asl
FROM
emp a , (select deptno,avg(sal)asl from emp group by deptno )b
where
a.deptno = b.deptno;


But , by the use of analytical function in here can simple the taks.

SELECTempno, deptno, sal,AVG(sal) OVER (PARTITION BY deptno) AS avg_dept_sal
FROM
emp;


it's simple to understand , you ask oracle to do the aggrigate with in the subset of data by using OVER (PARTITION BY ).

-- END --