TO FIND RUNNING TOTAL

To find the running total using the non-analytical way minimum the same table needs to be joined twice. Using Analytical functions the same can be achieved with single table hit. In a single query running total can be computed in no time.

NON ANALYTICAL WAY -

Select emp1.empno, emp1.sal, sum(emp2.sal) from scott.emp emp1, scott.emp emp2
where emp2.empno <= emp1.empno group by emp1.empno, emp1.sal
order by emp1.empno

EMPNO SAL SUM(EMP2.SAL)
---------- ---------- -------------
7369 800 800
7499 1600 2400
7521 1250 3650
7566 2975 6625
7654 1250 7875
7698 2850 10725
7782 2450 13175
7788 3000 16175
7839 5000 21175
7844 1500 22675
7876 1100 23775
7900 950 24725
7902 3000 27725
7934 1300 29025

14 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1016356015
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 160 | 7 (29)| 0
0:00:01 |
| 1 | SORT GROUP BY | | 10 | 160 | 7 (29)| 0
0:00:01 |
| 2 | MERGE JOIN | | 10 | 160 | 6 (17)| 0
0:00:01 |
| 3 | SORT JOIN | | 14 | 112 | 2 (0)| 0
0:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| EMP | 14 | 112 | 2 (0)| 0
0:00:01 |
| 5 | INDEX FULL SCAN | PK_EMP | 14 | | 1 (0)| 0
0:00:01 |
|* 6 | SORT JOIN | | 14 | 112 | 4 (25)| 0
0:00:01 |
| 7 | TABLE ACCESS FULL | EMP | 14 | 112 | 3 (0)| 0
0:00:01 |


Predicate Information (identified by operation id):
---------------------------------------------------

6 - access(INTERNAL_FUNCTION("EMP2"."EMPNO")<=INTERNAL_FUNCTION("EMP1"."EMPNO
" )) filter(INTERNAL_FUNCTION("EMP2"."EMPNO")<=INTERNAL_FUNCTION("EMP1"."EMPNO
" ))

Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
9 consistent gets
0 physical reads
0 redo size
790 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
14 rows processed

SQL>

ANALYTICAL WAY

SQL> set autotrace on
SQL> Select e1.empno, e1.sal, sum(e1.sal) OVER (ORDER BY rownum) from scott.emp
e1 order by e1.empno;

EMPNO SAL SUM(E1.SAL)OVER(ORDERBYROWNUM)
---------- ---------- ------------------------------
7369 800 800
7499 1600 2400
7521 1250 3650
7566 2975 6625
7654 1250 7875
7698 2850 10725
7782 2450 13175
7788 3000 16175
7839 5000 21175
7844 1500 22675
7876 1100 23775
7900 950 24725
7902 3000 27725
7934 1300 29025

14 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3990564813

-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 112 | 5 (40)| 00:00:01 |
| 1 | SORT ORDER BY | | 14 | 112 | 5 (40)| 00:00:01 |
| 2 | WINDOW SORT | | 14 | 112 | 5 (40)| 00:00:01 |
| 3 | COUNT | | | | | |
| 4 | TABLE ACCESS FULL| EMP | 14 | 112 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------


Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
7 consistent gets
0 physical reads
0 redo size
807 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
14 rows processed

SQL>