1

Running total

This post will show an example of how to join a table against itself to return a running total.

Schema
CREATE TABLE tbl (
  row_id tinyint PRIMARY KEY,
  amount decimal(6,2))
INSERT INTO tbl (row_id, amount)
VALUES
(1, '790.5'),
(2, '790.5'),
(3, '790.5'),
(4, '790.5'),
(5, '744'),
(6, '744'),
(7, '1348.5'),
(8, '1348.5'),
(9, '1348.5'),
(10, '1348.5'),
(11, '1302'),
(12, '1302'),
(13, '1302',
(14, '1302'),
(15, '1255.5'),
(16, '1209'),
(17, '1116'),
(18, '1116'),
(19, '1302'),
(20, '1302'),
(21, '1302'),
(22, '1255.5'),
(23, '1255.5'),
(24, '976.5')
Query
SELECT a.row_id, SUM(b.amount) AS RunningTotal FROM tbl a 
INNER JOIN tbl b ON b.row_id <= a.row_id
GROUP BY a.row_id
Result
row_id RunningTotal
-----  --------------
1      790.50
2      1581.00
3      2371.50
4      3162.00
5      3906.00
6      4650.00
7      5998.50
8      7347.00
9      8695.50
10     10044.00
11     11346.00
12     12648.00
13     13950.00
14     15252.00
15     16507.50
16     17716.50
17     18832.50
18     19948.50
19     21250.50
20     22552.50
21     23854.50
22     25110.00
23     26365.50
24     27342.00

Norbert Krupa

Technical Consultant

One Comment

  1. With SQL Server 2012 this can finally be done using window functions (like Oracle and PostgreSQL) which should perform better because only a single scan over the table is required:

    SELECT SUM(amount) over (order by row_id) AS RunningTotal 
    FROM tbl

Leave a Reply

Your email address will not be published.