Analytical SQL - Running Total - Order by without Partition By

Let's Develop in Oracle · 2 weeks ago · 40 views

Welcome to the third post on the series Analytical SQL. In the first two posts we studied Analytical Function without Window Clause Analytical Function Partition ByIn 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 As you can see in above output, data is ordered by HIREDATE, and RUNNING_TOTAL column is calculating SUM from the first row till the current row.