Computing running totals is one of the basic requirements when querying reports using SQL. Different database systems provide various ways to accomplish running total. Computing running total is, however, quite simple to query with the very basic elements of SQL. This sample uses a self join to calculate the subtotal for every row in a table. To run the example we will need a table with at least two columns. One column is an identifier to join the table instances and the other column is the value to sum up. First I create a table for monthly sales with a column for month number and another column for the actual sales value of each month.
create table sales (
mnth int,
sales decimal(9, 2)
);I then add some sample data for six months. This syntax is good for MySQL databases and with minor modifications compatible in other database systems as well.
insert into sales values
(1, 125),
(2, 100),
(3, 175),
(4, 150),
(5, 210),
(6, 190);The basic idea is to compute a subtotal in the inner query but only for rows already displayed. The final query computes the running total using the sum() function.
select s.mnth, s.sales, sum(t.sales) "running total"
from sales s
inner join (
select mnth, sales
from sales
group by mnth, sales
) t on t.mnth <= s.mnth
group by s.mnth, s.sales
order by s.mnth;A sample run reports values as planned.
mnth sales running total
---- ------ -------------
1 125.00 125.00
2 100.00 225.00
3 175.00 400.00
4 150.00 550.00
5 210.00 760.00
6 190.00 950.00Computing running total with this example requires an ordering column. It can be date, identifier, row number or equivalent value, which can be used to order the rows in ascending order. The same value is then used to join the the inner query to the outer one. This sample code snippet is very simple and easy to apply in SQL queries.