2

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

japzdivino
  • 1,736
  • 3
  • 17
  • 25
  • http://stackoverflow.com/questions/21746100/how-to-generate-fibonacci-series – willoller Aug 27 '15 at 03:52
  • @willoller thanks for that , but actually 'im looking on how to add values between two columns "column1 and result" like on the image above, not as simple as creating a fibonacci sequence. Something like fibonacci but addition of numbers is between two columns. – japzdivino Aug 27 '15 at 04:00
  • Are you asking for a running total? – Blorgbeard Aug 27 '15 at 04:04
  • Search for "SQL Recursive CTE* or *Recursive SQL Common Table Expression*. – Pieter Geerkens Aug 27 '15 at 04:08
  • @Blorgbeard , yup something like running total. do you have any sql query for that ? so that i can study the code and apply the logic on my side. Thanks – japzdivino Aug 27 '15 at 04:15
  • If you google for "sql running total", you'll find some helpful links. I think the fibonacci thing was a red-herring, and may have stopped you finding anything useful. – Blorgbeard Aug 27 '15 at 04:20
  • E.g. http://ole.michelsen.dk/blog/calculate-a-running-total-in-sql-server-2012.html – Blorgbeard Aug 27 '15 at 04:21
  • How you are ordering your data? Without order running total has no meaning. – Anup Agrawal Aug 27 '15 at 04:21
  • @Blorgbeard , yup actually i am continuously finding solution on my problem.. thanks for responses, much appreciated. – japzdivino Aug 27 '15 at 04:23
  • Do you have any id column with which you can order? – Sateesh Pagolu Aug 27 '15 at 04:25
  • @DarkKnight actually the ordering doesn't matter to me.. but if you are asking any id to be use for looping purposes, that is the same thing i wanna do, to insert in temp table with identity and make a loop with it, but my problem is how to do the logic of adding the values between the two columns to get the running total. – japzdivino Aug 27 '15 at 04:30
  • @AnupAgrawal , please see my comment to DarkKnight regarding order. – japzdivino Aug 27 '15 at 04:31

4 Answers4

7

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

Sabyasachi Mishra
  • 1,677
  • 2
  • 31
  • 49
  • Thank you so much Sabyasachi, it works! :) , i used this one since im using 2012 version.. and this makes lesser code and easy to understand. Thanks! – japzdivino Aug 28 '15 at 01:12
5

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

Sateesh Pagolu
  • 9,282
  • 2
  • 30
  • 48
  • Thank you so much.. this is exactly the logic im looking for, but i just wanna ask a question :) since i am new to CTE , is that a built-in function in sql ? , this is the first i am going to use that :) Thanks. – japzdivino Aug 27 '15 at 05:10
  • CTE is not keyword or function. Try googling for 'Common Table Expression'. you will see many references. cheers! – Sateesh Pagolu Aug 27 '15 at 05:32
2

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

Sabyasachi Mishra
  • 1,677
  • 2
  • 31
  • 49
1

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
Dan
  • 41
  • 2