Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


T-SQL Puzzle | Sum of All Previous Values

This puzzle is simple. Here we need to find out the sum of previous values. Please go through the sample input and expected output.

Sample Input

ID
1
2
3
4
5

Expected Output

ID SumPreValues
1 1
2 3 (Eg…2+1)
3 6
4 10
5 15

Rules/Restrictions

• The solution should be should use “SELECT” statement or “CTE”.
• Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

Script

Use the below script to generate the source table and fill them up with the sample data.


CREATE TABLE List
(
ID INT
)
GO

INSERT INTO List(ID) VALUES (1),(2),(3),(4),(5)

Update May 14 | Solutions


--


---------------------------------------
--Sol 1 | Pawan Kumar Khowal
---------------------------------------


SELECT *, SUM(ID) OVER (ORDER BY %%Physloc%%) Sums 
FROM List

---------------------------------------
--Sol 2 | Pawan Kumar Khowal
---------------------------------------


;WITH CTE AS
(
	SELECT *, ROW_NUMBER() OVER (ORDER BY %%Physloc%%) rnk 
	FROM List
)
SELECT ID ,  SUM(ID) OVER (ORDER BY rnk) Sums FROM CTE

---------------------------------------
--Sol 3 | Pawan Kumar Khowal
---------------------------------------


;WITH CTE AS
(
	SELECT *, ROW_NUMBER() OVER (ORDER BY %%Physloc%%) rnk
	FROM List
)
SELECT ID , (SELECT SUM(ID) FROM CTE c2 WHERE c2.rnk <= c1.rnk) Sums
FROM CTE c1


--

Add a comment if you have any other solution in mind. We all need to learn.

Keep Learning

http://MSBISkills.com

Advertisements