I am trying to calculate the val column using id column and the previous val column. On Excel, I simply use the formula "=IF(B2=1,1,C1+B2)". How can i reference previous value of my column being computed?
id val
1 1
2 3
3 6
4 10
5 15
6 21
7 28
8 36
9 45
10 55
My query would look like
SELECT id,
Case
When id =1 then 1
Else id+*previousval*
end as val
from
tab
You can try this solution for SQL Server (using variable will give you a best performance):
DECLARE @TempTable TABLE (Id INT, Val INT)
INSERT INTO @TempTable VALUES(1,0)
INSERT INTO @TempTable VALUES(2,0)
INSERT INTO @TempTable VALUES(3,0)
INSERT INTO @TempTable VALUES(4,0)
INSERT INTO @TempTable VALUES(5,0)
INSERT INTO @TempTable VALUES(6,0)
INSERT INTO @TempTable VALUES(7,0)
DECLARE @PrevSum INT = 0
UPDATE @TempTable
SET @PrevSum = Val = @PrevSum + Id
FROM @TempTable
SELECT * FROM @TempTable
It is better if you can tag your DBMS and its version..
Assuming that you are using SQL Server:
SQL Server BELOW 2012 (you can use the Common Table Expression - CTE)
CREATE TABLE #TEST(ID INT)
INSERT INTO #TEST VALUES
(1),(2),(3),(4),(5),(6),(7),(8),(9),(10)
;WITH CTE
as
(
SELECT ID, ID AS VALUE FROM #TEST WHERE ID=1
UNION ALL
SELECT T.ID, T.ID + C.VALUE
FROM #TEST T INNER JOIN CTE C ON T.ID = C.ID+1
)
SELECT * FROM CTE
DROP TABLE #TEST
SQL Fiddle for version 2012 Below
SQL Server 2012: (you can use OVER clause)
CREATE TABLE #Test(ID INT)
INSERT INTO #Test VALUES
(1),(2),(3),(4),(5),(6),(7),(8),(9),(10)
SELECT id, SUM(id) OVER(ORDER BY id) AS [Value] FROM #Test ORDER BY id
DROP TABLE #Test
