There are applications where the running total for a series of numbers needs to be calculated and displayed. While this might be normally considered something that would be done with 3GL programming techniques, it IS possible to calculate and query a running total with SQL only.
First, we need to have a ‘key’ column to order the query by.
Let’s assume we have a table with a numeric column that we want. We also need to have a column to order the display by, or a running total does not make sense! Many times this will be a date column. This could also be the PK of the table, or a single or multiple column UK. I will use a date column in the following example.
Consider the table:
SQL> desc Run_Total
Name Null? Type
——————————- ——– —-
RUN_DATE DATE
RUN_VALUE NUMBER
SQL> select * from Run_Total;
RUN_DATE RUN_VALUE
——— ———
02-APR-97 10
03-APR-97 5
04-APR-97 20
05-APR-97 15
06-APR-97 45
07-APR-97 12
08-APR-97 37
09-APR-97 9
10-APR-97 23
11-APR-97 19
12-APR-97 10
11 rows selected.
A self-join is needed to perform the correct sum.
In order to produce the running total, a self-join is needed to sum all of the values of the table less than or equal to each row. The query is grouped by the records in the ‘driving’ table in the join, and ordered by the run_date:
SELECT r1.run_date, r1.run_value, sum(r2.run_value) Running_Total
FROM Run_Total r1, Run_Total r2
WHERE r2.run_date <= r1.run_date
GROUP BY r1.run_date, r1.run_value
ORDER BY r1.run_date
/
This query will produce the following output:
RUN_DATE RUN_VALUE RUNNING_TOTAL
--------- --------- -------------
02-APR-97 10 10
03-APR-97 5 15
04-APR-97 20 35
05-APR-97 15 50
06-APR-97 45 95
07-APR-97 12 107
08-APR-97 37 144
09-APR-97 9 153
10-APR-97 23 176
11-APR-97 19 195
12-APR-97 10 205
11 rows selected.