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.

How to create running totals with SQL

Leave a Reply

Your email address will not be published. Required fields are marked *

+ six = 12