Curious about using Oracle Analytical Functions


Getting Cumulative sum using Oracle Analytical Functions


1) The following code gives cumulative sum. The following code uses the SAL column from the standard EMP table
SQL> select b.sal,sum(a.sal) as cum_sal2 from emp a,emp b3 where a.rowid <= b.rowid4 group by b.rowid,b.sal
5 /

I found the code in the following link.
http://www.oracle.com/technology/oramag/code/tips2005/052305.html

2) Alternative Way to find Cumulative sum using Analytical Functions
SQL> select deptno, 2 ename, 3 sal, 4 sum(sal) over (partition by deptno 5 order by sal,ename) CumDeptTot, 6 sum(sal) over (partition by deptno) SalByDept, 7 sum(sal) over (order by deptno, sal) CumTot, 8 sum(sal) over () TotSal 9 from emp 10 order by deptno, sal;

a. CumDeptTot – This Sum will give the cumulative sum for each Department
b. SalByDept - This will give the Sum of Salary for Each Department

Description by the Author

sum(sal) over (partition by deptno order by sal, ename) CumDeptTot: Computes a running total on salaries within a department, in order of salaries from lowest salary to the highest. Adding the ORDER BY clause changed the behavior of the analytic function. Instead of working on every row in the partition, it worked only on the current and prior rows in the partition after sorting by SAL.

sum(sal) over (partition by deptno) SalByDept: Computes a department total. The SAL column was summed by department now. The individual EMP records can compare their salary against the total salary made in that department. The PARTITION keyword breaks up our result set into virtual partitions on which the analytic functions will be applied.

sum(sal) over (order by deptno, sal) CumTot: Creates a running total of the SAL column after the data was sorted by DEPTNO and then SAL. Since out entire result set would be sorted this way, it becomes a running total for our entire result set.

sum(sal) over () TotSal: Computes a grand total. This is equivalent to select sum(sal) from emp, but that query would return only one row. Here we get the sum of SAL associated with each detail row. We can easily compare each salary in the EMP table to the grand total sum of SAL.

Source: http://www.oracle.com/technology/oramag/code/tips2005/062005.html