CODEWITHSUNDEEP
sqlsql-server-2012cumulative-sum
japzdivino
japzdivino

Reputation: 1746

How to continously add values of starting row and next row to it

i just want to create an sql query and the result is something like on the image., something like Fibonacci sequence in SQL.

Ex.

Column 1: 10 , then the value of Result column is Result: 10 , since that is the first row. , then assuming that the value of column1 2nd row is 50, then the value of Result 2nd row will be 60.. (Result: 60).. and so on.

Sample is the image below.

How can i do that continuously ? any help would be appreciated. Thanks

enter image description here

Upvotes: 2

Views: 9239

Answers (4)

Sabyasachi Mishra
Sabyasachi Mishra

Reputation: 1749

If you are using MSSQL2012 or higher you can use OVER clause.

SELECT t2.id, t2.value, SUM(t2.value) OVER (ORDER BY t2.id) as [Result]
FROM   Test01 t2
ORDER BY t2.id;

sql fiddle demo

Upvotes: 7

Sabyasachi Mishra
Sabyasachi Mishra

Reputation: 1749

Try this

select Id, value,
(select sum(t2.value) from TEST01 t2 where t2.id <= t1.id ) 
as Result
from TEST01 t1

Find the solution in fiddle http://sqlfiddle.com/#!6/a8f56/2

Upvotes: 2

Dan
Dan

Reputation: 41

You could also use a window function.

DECLARE @myTable TABLE(ID INT, val INT);

INSERT INTO @myTable VALUES (1,10),
                            (2,7),
                            (3,-4),
                            (4,1);
SELECT ID, 
       val,
       SUM(val) OVER (ORDER BY ID 
                      ROWS BETWEEN UNBOUNDED PRECEDING 
                      AND CURRENT ROW) AS result
FROM @myTable
ORDER BY ID;

ID  val  result
--  ---  ------
1   10   10
2   7    17
3   -4   13
4   1    14

Upvotes: 1

Sateesh Pagolu
Sateesh Pagolu

Reputation: 9606

You can try this

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

Result

Upvotes: 5

Related Questions