SQL Puzzle: SQL Advance Query - Find the Sum of privious values without using a self-join

SQL Puzzle: SQL Advance Query – Find the Sum of privious values without using a self-join

This article is half-done without your Comment! *** Please share your thoughts via Comment ***

Check the below input data and require output data to calculate the sum of previous values without using a self-join.

Input Data:

-----------

Expected Output:

ID SumsList

----------- -----------

1 1

2 3

3 6

4 10

5 15

6 21

7 28

Create a table with sample data:

CREATE TABLE SumOfPrev(ID INT)

INSERT INTO SumOfPrev

VALUES (1),(2),(3),(4),(5),(6),(7)

Solution 1: Using SUM() OVER()

1 2	SELECT *, SUM(ID) OVER (ORDER BY ID) SumsList FROM SumOfPrev

Solution 2: Using CTE

;WITH CTE AS

(

SELECT *, ROW_NUMBER() OVER (ORDER BY ID) rnk

FROM SumOfPrev

)

SELECT ID, SUM(ID) OVER (ORDER BY rnk) SumsList

FROM CTE

Please try the different solution for this puzzle and share it via comment...

Nov 20, 2017Anvesh Patel

Comments: 1

Dinesh I.S

November 23, 2017 at 10:35 am

Select ID,Sum(ID)Over(Order By ID ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) As A
From SumOfPrev

SQL Puzzle: SQL Advance Query – Find the Sum of privious values without using a self-join