Tags
Complex SQL Challenges, Complex TSQL Challenge, How to find sum of all previous values in SQL, Interview Qs.SQL SERVER Questions, Interview Questions on SQL, InterviewQuestions, InterviewQuestions for SQL, Learn complex SQL, Learn SQL, Learn T-SQL, PL/SQL Challenges, Puzzles, Queries for SQL Interview, SQL, SQL 2012, SQL Challenge, SQL Challenges, SQL pl/sql puzzles, SQL Puzzles, SQL SERVER Interview questions, SQL SERVER2005/2008, SQL Sudoku, SQLSERVER, Sum of previous values in SQL, Sum of previous values in SQL SERVER, T SQL Puzzles, T-SQL Challenge, Tough SQL Challenges, Tough SQL Puzzles, TSQL, TSQL Challenge, TSQL Challenges, TSQL Interview questions, TSQL Queries
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
Improving my SQL BI Skills 
WITH CTE AS
(
select ID ,ROW_NUMBER() OVER(order by %%Physloc%%) rownum
from List
)
select
(SELECT SUM (ID) from CTE b where a.rownum >= b.rownum)
FROM CTE a
LikeLike
SELECT *, SUM(ID) OVER (ORDER BY ID) Sums
FROM List
LikeLike
select a.id,sum(b.id) as sum
from list a
inner join list b
on a.id>=b.id
group by a.id
LikeLike
with cte as
(
select Id,1 as SumPreValues
from List
where id=1
union all
select l.Id,c.SumPreValues+l.ID as SumPreValues
from cte as c inner join List as l
on c.Id=l.ID-1
)
LikeLiked by 1 person
select l.ID,
l.id + isnull((select sum(j.id) from list j where j.id < l.id),0) as RunningTotal
from list l
LikeLike