Recipe 7.6. Generating a Running Total:

Solution

As an example, the following solutions show how to compute a running total of salaries for all employees. For readability, results are ordered by SAL whenever possible so that you can easily eyeball the progression of the running total.
DB2 and Oracle

Use the windowing version of the function SUM to compute a running total:


1 select ename, sal,
2 sum(sal) over (order by sal,empno) as running_total
3 from emp
4 order by 2


ENAME SAL RUNNING_TOTAL
---------- ---------- -------------
SMITH 800 800
JAMES 950 1750
ADAMS 1100 2850
WARD 1250 4100
MARTIN 1250 5350
MILLER 1300 6650
TURNER 1500 8150
ALLEN 1600 9750
CLARK 2450 12200
BLAKE 2850 15050
JONES 2975 18025
SCOTT 3000 21025
FORD 3000 24025
KING 5000 29025


MySQL, PostgreSQL, and SQL Server

Use a scalar subquery to compute a running total (without the use of a window function such as SUM OVER, you cannot easily order the result set by SAL as in the DB2 and Oracle solution). Ultimately, the running total is correct (the final value is the same as the above recipe), but the intermediate values differ due to the lack of ordering:


1 select e.ename, e.sal,
2 (select sum(d.sal) from emp d
3 where d.empno <= e.empno) as running_total
4 from emp e
5 order by 3

ENAME SAL RUNNING_TOTAL
---------- ---------- -------------
SMITH 800 800
ALLEN 1600 2400
WARD 1250 3650
JONES 2975 6625
MARTIN 1250 7875
BLAKE 2850 10725
CLARK 2450 13175
SCOTT 3000 16175
KING 5000 21175
TURNER 1500 22675
ADAMS 1100 23775
JAMES 950 24725
FORD 3000 27725
MILLER 1300 29025