12,698,012 members (24,742 online)

253.4K views
46 bookmarked
Posted

# Calculating simple running totals in SQL Server

, 12 Dec 2014 CPOL
Some simple scenarios to calculate running totals in SQL Server.
 ```-------------------------------------------------------------------- -- table for test -------------------------------------------------------------------- CREATE TABLE RunTotalTestData ( id int not null identity(1,1) primary key, value int not null ); -------------------------------------------------------------------- -- test data -------------------------------------------------------------------- INSERT INTO RunTotalTestData (value) VALUES (1); INSERT INTO RunTotalTestData (value) VALUES (2); INSERT INTO RunTotalTestData (value) VALUES (4); INSERT INTO RunTotalTestData (value) VALUES (7); INSERT INTO RunTotalTestData (value) VALUES (9); INSERT INTO RunTotalTestData (value) VALUES (12); INSERT INTO RunTotalTestData (value) VALUES (13); INSERT INTO RunTotalTestData (value) VALUES (16); INSERT INTO RunTotalTestData (value) VALUES (22); INSERT INTO RunTotalTestData (value) VALUES (42); INSERT INTO RunTotalTestData (value) VALUES (57); INSERT INTO RunTotalTestData (value) VALUES (58); INSERT INTO RunTotalTestData (value) VALUES (59); INSERT INTO RunTotalTestData (value) VALUES (60); -------------------------------------------------------------------- -- contents -------------------------------------------------------------------- SELECT * FROM RunTotalTestData; -------------------------------------------------------------------- -- correlated scalar -------------------------------------------------------------------- SELECT a.id, a.value, (SELECT sum(b.value) FROM RunTotalTestData b WHERE b.id <= a.id) FROM RunTotalTestData a ORDER BY a.id; -------------------------------------------------------------------- -- using join -------------------------------------------------------------------- SELECT a.id, a.value, SUM(b.Value) FROM RunTotalTestData a, RunTotalTestData b WHERE b.id <= a.id GROUP BY a.id, a.value ORDER BY a.id; -------------------------------------------------------------------- -- correlated scalar, subset -------------------------------------------------------------------- SELECT a.id, a.value, (SELECT sum(b.value) FROM RunTotalTestData b WHERE b.id <= a.id AND b.value % 2 = 1) FROM RunTotalTestData a WHERE a.value % 2 = 1 ORDER BY a.id; -------------------------------------------------------------------- -- with join, subset -------------------------------------------------------------------- SELECT a.id, a.value, SUM(b.Value) FROM RunTotalTestData a, RunTotalTestData b WHERE b.id <= a.id AND a.value % 2 = 1 AND b.value % 2 = 1 GROUP BY a.id, a.value ORDER BY a.id; -------------------------------------------------------------------- -- correlated scalar, partitioning -------------------------------------------------------------------- SELECT a.value%2, a.id, a.value, (SELECT sum(b.value) FROM RunTotalTestData b WHERE b.id <= a.id AND b.value%2 = a.value%2) FROM RunTotalTestData a ORDER BY a.value%2, a.id; -------------------------------------------------------------------- -- with join, partitioning -------------------------------------------------------------------- SELECT a.value%2, a.id, a.value, SUM(b.Value) FROM RunTotalTestData a, RunTotalTestData b WHERE b.id <= a.id AND b.value%2 = a.value%2 GROUP BY a.value%2, a.id, a.value ORDER BY a.value%2, a.id; -------------------------------------------------------------------- -- Using OVER clause -------------------------------------------------------------------- SELECT a.id, a.value, sum(a.value) OVER (ORDER BY a.id) FROM RunTotalTestData a ORDER BY a.id; -------------------------------------------------------------------- -- Using OVER clause, subset -------------------------------------------------------------------- SELECT a.id, a.value, sum(a.value) OVER (ORDER BY a.id) FROM RunTotalTestData a WHERE a.value % 2 = 1 ORDER BY a.id; -------------------------------------------------------------------- -- Using OVER clause, partition -------------------------------------------------------------------- SELECT a.value%2, a.id, a.value, SUM(a.value) OVER (PARTITION BY a.value%2 ORDER BY a.id) FROM RunTotalTestData a ORDER BY a.value%2, a.id;```

If a file you wish to view isn't highlighted, and is a text file (not binary), please let us know and we'll add colourisation support for it.