Thursday, 9 May 2019

Creating running sum & getting actual values from running sum

This post explains how to create RUNNING_SUM (Cumulative sum) of salaries of employee and how to get the actual values from RUNNING_SUM 

SELECT ENAME, SAL, CUMLATIVE_SAL,
COALESCE ((CUMLATIVE_SAL - LAG (CUMLATIVE_SAL, 1) OVER (ORDER BY CUMLATIVE_SAL)), CUMLATIVE_SAL) AS GETTING_ACTUAL
FROM
(SELECT ENAME, SAL, SUM (SAL) OVER (ORDER BY SAL ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS CUMLATIVE_SAL
FROM SCOTT.EMP ORDER BY DEPTNO ASC, SAL ASC
)


------------------------------------------






No comments:

Post a Comment