Recipe7.8.Calculating a Running Difference

Recipe 7.8. Calculating a Running Difference

Problem

You want to compute a running difference on values in a numeric column. For example, you want to compute a running difference on the salaries in DEPTNO 10. You would like to return the following result set:

 ENAME             SAL RUNNING_DIFF ---------- ---------- ------------ MILLER           1300         1300 CLARK            2450        -1150 KING             5000        -6150 

Solution

DB2 and Oracle

Use the window function SUM OVER to create a running difference:

 1 select ename,sal, 2        sum(case when rn = 1 then sal else -sal end) 3         over(order by sal,empno) as running_diff 4   from ( 5 select empno,ename,sal, 6        row_number()over(order by sal,empno) as rn 7   from emp 8  where deptno = 10 9        ) x 

MySQL, PostgreSQL, and SQL Server

Use a scalar subquery to compute a running difference:

 1 select a.empno, a.ename, a.sal, 2        (select case when a.empno = min(b.empno) then sum(b.sal) 3                     else sum(-b.sal) 4                end 5           from emp b 6          where b.empno <= a.empno 7            and b.deptno = a.deptno ) as rnk 8   from emp a 9  where a.deptno = 10 

Discussion

The solutions are identical to those of "Generating a Running Total." The only difference is that all values for SAL are returned as negative values with the exception of the first (you want the starting point to be the first SAL in DEPTNO 10).