Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Calculate a running total in SQL Server 2012
CREATE TABLE #TestData (
id int not null identity(1,1) primary key,
account varchar(10) not null,
deposit int not null
);
INSERT INTO #TestData (account, deposit) VALUES ('Vacation', 10)
INSERT INTO #TestData (account, deposit) VALUES ('Vacation', 20)
INSERT INTO #TestData (account, deposit) VALUES ('Vacation', 30)
INSERT INTO #TestData (account, deposit) VALUES ('Bills', 40)
INSERT INTO #TestData (account, deposit) VALUES ('Bills', 50)
INSERT INTO #TestData (account, deposit) VALUES ('Bills', 60)
INSERT INTO #TestData (account, deposit) VALUES ('Party', 70)
INSERT INTO #TestData (account, deposit) VALUES ('Party', 80)
--------------------------------------------------------------------
-- Using OVER clause
--------------------------------------------------------------------
SELECT a.id, a.account, a.deposit, SUM(a.deposit) OVER (ORDER BY a.id) AS 'total'
FROM #TestData a
ORDER BY a.id;
--------------------------------------------------------------------
-- Using OVER clause, partition
--------------------------------------------------------------------
SELECT a.id, a.account, a.deposit, SUM(a.deposit) OVER (PARTITION BY a.account ORDER BY a.id) AS 'total'
FROM #TestData a
ORDER BY a.id;
--------------------------------------------------------------------
-- Using sub query
--------------------------------------------------------------------
SELECT a.*,
(SELECT SUM(b.deposit) FROM #TestData b WHERE b.id <= a.id) AS 'total'
FROM #TestData a
--------------------------------------------------------------------
-- Using CTE
--------------------------------------------------------------------
;WITH cte AS (
SELECT id, account, deposit, deposit as 'total'
FROM #TestData
WHERE id = 1
UNION ALL
SELECT a.id, a.account, a.deposit, cte.total + a.deposit
FROM cte JOIN #TestData a ON cte.id + 1 = a.id
)
SELECT * FROM cte
OPTION (MAXRECURSION 32767);
DROP TABLE #TestData
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.