Running Total query in sql

                          Running Total query in sql

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 ('Ram', 10)
INSERT INTO #TestData (account, deposit) VALUES ('Ram', 20)
INSERT INTO #TestData (account, deposit) VALUES ('Ram', 30)
INSERT INTO #TestData (account, deposit) VALUES ('Shyam', 40)
INSERT INTO #TestData (account, deposit) VALUES ('Shyam', 50)
INSERT INTO #TestData (account, deposit) VALUES ('Shyam', 60)
INSERT INTO #TestData (account, deposit) VALUES ('Krishan', 70)
INSERT INTO #TestData (account, deposit) VALUES ('Krishan', 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