Kindly help me with finding the running total for the below table
http://sqlfiddle.com/#!3/2c0ec/1
The example shown above is a sample. The actual query used to arrive at this place is quite large, hence self join is not a viable option. Kindly suggest a function like sum() over(.....)
Following will be the output
thanks in advance
The standard way is to use subquery:
select *, (select sum(salary) from tablename t2 where t2.id <= t1.id) as runningsalary
from tablename t1
In Sql Server 2012 + you can use window function for this:
select *, sum(salary) over(order by id) as runningsalary
from tablename t1
I ask that before, You can use Common Table Expression (CTE) in 2008 version, and OVER clause in 2012
Here's is one answer i get from Dark Knight.
CREATE TABLE #TEST(ID INT,VALUE INT)
INSERT INTO #TEST VALUES
(1,10),(2,20),(3,30),(4,40),(5,50),(6,60),(7,70)
;WITH CTE
as
(
SELECT ID,VALUE,VALUE AS RESULT FROM #TEST WHERE ID=1
UNION ALL
SELECT T.ID,T.VALUE,T.VALUE+C.RESULT
FROM #TEST T INNER JOIN CTE C ON T.ID = C.ID+1
)
SELECT * FROM CTE
credits: https://stackoverflow.com/a/32240745/5197152
How to continously add values of starting row and next row to it
Hope that thread will help you.
