1

Running total

Posted on August 14, 2012 by Norbert Krupa

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

More Posts Twitter LinkedIn

Reply

January 13, 2013
Hans
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
SELECT SUM(amount) over (order by row_id) AS RunningTotal FROM tbl

Leave a Reply Cancel reply