|
Tip of the Week
Tip for Week of June 20, 2005
Getting Cumulative Sum Using Oracle Analytical Functions
This tip comes from
Robert Ware, DBA at TekSystems, in St. Louis, Missouri.
The following code is an alternative way to get a cumulative sum by using Oracle Analytical Function. (This is an alternative approach to the "Getting Cumulative Sum" code tip of May 23, 2005.)
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;
DEPTNO ENAME SAL CUMDEPTTOT SALBYDEPT CUMTOT TOTSAL
---------- ---------- ---------- ---------- ---------- ---------- ----------
10 MILLER 1300 1300 8750 1300 29025
10 CLARK 2450 3750 8750 3750 29025
10 KING 5000 8750 8750 8750 29025
20 SMITH 800 800 10875 9550 29025
20 ADAMS 1100 1900 10875 10650 29025
20 JONES 2975 4875 10875 13625 29025
20 FORD 3000 7875 10875 19625 29025
20 SCOTT 3000 10875 10875 19625 29025
30 JAMES 950 950 9400 20575 29025
30 MARTIN 1250 2200 9400 23075 29025
30 WARD 1250 3450 9400 23075 29025
30 TURNER 1500 4950 9400 24575 29025
30 ALLEN 1600 6550 9400 26175 29025
30 BLAKE 2850 9400 9400 29025 29025
14 rows selected.
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.
|
Bookmark
