Running query in sql server

C# Corner

Sai Reddy

8y1k

Input:

ID Value

1 10

2 20

3 30

4 40

5 50

Output:

ID Value

1 10

2 30(10+20)

3 50(20+30)

4 70(30+40)

5 90(40+50)

Please help me to here?

8 Replies

Know the answer? Post it — somebody with the same question will find it here.

Jayesh Vaghasiya8y ago
DECLARE @table AS TABLE (ID Int,Value int)

INSERT INTO @table VALUES (1, 10),(2, 20),(3, 30),(4, 40),(5, 50)
SELECT t.ID,isnull(t.Value,0) + isnull(t1.Value,0) as Value
FROM @table t
left JOIN @table t1 ON t.ID = t1.ID+1
SELECT t.ID,CONVERT(varchar(16),isnull(t.Value,0) + isnull(t1.Value,0)) + case when isnull(t1.Value,0) = 0 then '' else '(' + CONVERT(varchar(16),isnull(t1.Value,0)) + '+' + CONVERT(varchar(16),isnull(t.Value,0)) +')' end --as Value
FROM @table t
left JOIN @table t1 ON t.ID = t1.ID+1 --order by t.ID;
0
Jayesh Vaghasiya8y ago
DECLARE @table AS TABLE (ID Int,Value int)

INSERT INTO @table VALUES (1, 10),(2, 20),(3, 30),(4, 40),(5, 50)
SELECT t.ID,isnull(t.Value,0) + isnull(t1.Value,0) as Value
FROM @table t
left JOIN @table t1 ON t.ID = t1.ID+1
0
Shahriar Nikkhah8y ago
try this
https://www.codeproject.com/Articles/37468/Sum-of-previous-row-values-in-SqlServer
+1
sudipta sanyal8y ago
1. DECLARE @TestTable TABLE (
2. ID INT ,
3. Value DECIMAL(18,2)
4. )
5. INSERT INTO @TestTable (ID, Value)
6. SELECT 1, 10
7. UNION ALL
8. SELECT 2, 20
9. UNION ALL
10. SELECT 3, 30
11. UNION ALL
12. SELECT 4, 40
13. UNION ALL
14. SELECT 5, 50
15. UNION ALL
16. SELECT 6, 60
17. UNION ALL
18. SELECT 7, 70
19. -- IF Using < 2012 Sql Server
20. SELECT ID, Value,
21. (SELECT SUM(b.Value) FROM @TestTable b WHERE b.id <= a.id) AS 'total'
22. FROM @TestTable a
23. -- IF Using >= 2012 Sql Server
24. SELECT ID, Value,SUM(Value) OVER (ORDER BY id) AS 'total'
25. FROM @TestTable
+1