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
@NasirJamal786

This comment has been minimized.

Copy link

NasirJamal786 commented Nov 20, 2019

i am working with your first option but.. when i want to filter data between dates it does not meet requirement because
it sum only the selected filtering dates it does not calculate previous sum but i need also sum from first entry...

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.