Advantage Developer Zone

 
 
 

How To Compute A Running Total With SQL

Thursday, August 14, 2003

You can compute a running total with a single SQL statement by performing a join on a table with itself. The join condition should be based on a unique field value otherwise the summation for the non-unique values will be combined for the duplicate entries. The following illustrates the idea with a simple example.

/* Create a test table with a few rows of data */
create table test( id integer, amount double );
insert into test values ( 1, 10 );
insert into test values ( 2, 15 );
insert into test values ( 3, 20 );
insert into test values ( 4, 18 );
create unique index id on test( id );

/* Select the desired fields. Perform the sum on
* the desired column in the second table. */
SELECT t1.ID, t1.amount, sum(t2.amount) as running_total
FROM test t1, test t2
/* For each row in t1, we want all the rows
* in t2 up to and including the current row */
WHERE t2.ID <= t1.ID
/* Group by the non-aggregate columns */
GROUP by 1, 2
/* order by the column by which the running total
* is controlled. */
ORDER by 1;



The results of this query are:
ID amount running_total
-------------------------------------
1 10 10
2 15 25
3 20 45
4 18 63