In this post we will create a report showing a running total of the complete EMP table in order of HIREDATE.
To create a running total for the complete dataset, PARTITION BY is not required, we just need to add ORDER BY in OVER analytic clause
SQL> select d.deptno, d.dname, empno, ename, hiredate, sal,
2 sum(sal) over(order by hiredate) running_total
3 from emp e, dept d
4 where e.deptno = d.deptno;
DEPTNO DNAME EMPNO ENAME HIREDATE SAL RUNNING_TOTAL
---------- -------------- ---------- ---------- --------- ---------- -------------
20 RESEARCH 7369 SMITH 17-DEC-80 800.75 800.75
30 SALES 7499 ALLEN 20-FEB-81 1600.9 2401.65
30 SALES 7521 WARD 22-FEB-81 1250 3651.65
20 RESEARCH 7566 JONES 02-APR-81 2975.5 6627.15
30 SALES 7698 BLAKE 01-MAY-81 2850.3 9477.45
10 ACCOUNTING 7782 CLARK 09-JUN-81 2450.45 11927.9
30 SALES 7844 TURNER 08-SEP-81 1500.95 13428.85
30 SALES 7654 MARTIN 28-SEP-81 1250.05 14678.9
10 ACCOUNTING 7839 KING 17-NOV-81 5000.15 19679.05
20 RESEARCH 7902 FORD 03-DEC-81 3000.6 23630.64
30 SALES 7900 JAMES 03-DEC-81 950.99 23630.64
10 ACCOUNTING 7934 MILLER 23-JAN-82 1300 24930.64
20 RESEARCH 7788 SCOTT 19-APR-87 3000.55 27931.19
20 RESEARCH 7876 ADAMS 23-MAY-87 1100.01 29031.2
