declare @t table ( id int, SomeNumt int ) insert into @t select 1,10 union select 2,12 union select 3,3 union select 4,15 union select 5,23 select * from @t the above select returns me the following.
id SomeNumt 1 10 2 12 3 3 4 15 5 23 How do I get the following:
id srome CumSrome 1 10 10 2 12 22 3 3 25 4 15 40 5 23 63 16 Answers
select t1.id, t1.SomeNumt, SUM(t2.SomeNumt) as sum from @t t1 inner join @t t2 on t1.id >= t2.id group by t1.id, t1.SomeNumt order by t1.id Output
| ID | SOMENUMT | SUM | ----------------------- | 1 | 10 | 10 | | 2 | 12 | 22 | | 3 | 3 | 25 | | 4 | 15 | 40 | | 5 | 23 | 63 | Edit: this is a generalized solution that will work across most db platforms. When there is a better solution available for your specific platform (e.g., gareth's), use it!
4The latest version of SQL Server (2012) permits the following.
SELECT RowID, Col1, SUM(Col1) OVER(ORDER BY RowId ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS Col2 FROM tablehh ORDER BY RowId or
SELECT GroupID, RowID, Col1, SUM(Col1) OVER(PARTITION BY GroupID ORDER BY RowId ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS Col2 FROM tablehh ORDER BY RowId This is even faster. Partitioned version completes in 34 seconds over 5 million rows for me.
Thanks to Peso, who commented on the SQL Team thread referred to in another answer.
4For SQL Server 2012 onwards it could be easy:
SELECT id, SomeNumt, sum(SomeNumt) OVER (ORDER BY id) as CumSrome FROM @t because ORDER BY clause for SUM by default means RANGE UNBOUNDED PRECEDING AND CURRENT ROW for window frame ("General Remarks" at )
Let's first create a table with dummy data:
Create Table CUMULATIVESUM (id tinyint , SomeValue tinyint) Now let's insert some data into the table;
Insert Into CUMULATIVESUM Select 1, 10 union Select 2, 2 union Select 3, 6 union Select 4, 10 Here I am joining same table (self joining)
Select c1.ID, c1.SomeValue, c2.SomeValue From CumulativeSum c1, CumulativeSum c2 Where c1.id >= c2.ID Order By c1.id Asc Result:
ID SomeValue SomeValue ------------------------- 1 10 10 2 2 10 2 2 2 3 6 10 3 6 2 3 6 6 4 10 10 4 10 2 4 10 6 4 10 10 Here we go now just sum the Somevalue of t2 and we`ll get the answer:
Select c1.ID, c1.SomeValue, Sum(c2.SomeValue) CumulativeSumValue From CumulativeSum c1, CumulativeSum c2 Where c1.id >= c2.ID Group By c1.ID, c1.SomeValue Order By c1.id Asc For SQL Server 2012 and above (much better performance):
Select c1.ID, c1.SomeValue, Sum (SomeValue) Over (Order By c1.ID ) From CumulativeSum c1 Order By c1.id Asc Desired result:
ID SomeValue CumlativeSumValue --------------------------------- 1 10 10 2 2 12 3 6 18 4 10 28 Drop Table CumulativeSum A CTE version, just for fun:
; WITH abcd AS ( SELECT id ,SomeNumt ,SomeNumt AS MySum FROM @t WHERE id = 1 UNION ALL SELECT t.id ,t.SomeNumt ,t.SomeNumt + a.MySum AS MySum FROM @t AS t JOIN abcd AS a ON a.id = t.id - 1 ) SELECT * FROM abcd OPTION ( MAXRECURSION 1000 ) -- limit recursion here, or 0 for no limit. Returns:
id SomeNumt MySum ----------- ----------- ----------- 1 10 10 2 12 22 3 3 25 4 15 40 5 23 63 Late answer but showing one more possibility...
Cumulative Sum generation can be more optimized with the CROSS APPLY logic.
Works better than the INNER JOIN & OVER Clause when analyzed the actual query plan ...
/* Create table & populate data */ IF OBJECT_ID('tempdb..#TMP') IS NOT NULL DROP TABLE #TMP SELECT * INTO #TMP FROM ( SELECT 1 AS id UNION SELECT 2 AS id UNION SELECT 3 AS id UNION SELECT 4 AS id UNION SELECT 5 AS id ) Tab /* Using CROSS APPLY Query cost relative to the batch 17% */ SELECT T1.id, T2.CumSum FROM #TMP T1 CROSS APPLY ( SELECT SUM(T2.id) AS CumSum FROM #TMP T2 WHERE T1.id >= T2.id ) T2 /* Using INNER JOIN Query cost relative to the batch 46% */ SELECT T1.id, SUM(T2.id) CumSum FROM #TMP T1 INNER JOIN #TMP T2 ON T1.id > = T2.id GROUP BY T1.id /* Using OVER clause Query cost relative to the batch 37% */ SELECT T1.id, SUM(T1.id) OVER( PARTITION BY id) FROM #TMP T1 Output:- id CumSum ------- ------- 1 1 2 3 3 6 4 10 5 15 Select *, (Select Sum(SOMENUMT) From @t S Where S.id <= M.id) From @t M You can use this simple query for progressive calculation :
select id ,SomeNumt ,sum(SomeNumt) over(order by id ROWS between UNBOUNDED PRECEDING and CURRENT ROW) as CumSrome from @t There is a much faster CTE implementation available in this excellent post:
The problem in this thread can be expressed like this:
DECLARE @RT INT SELECT @RT = 0 ; WITH abcd AS ( SELECT TOP 100 percent id ,SomeNumt ,MySum order by id ) update abcd set @RT = MySum = @RT + SomeNumt output inserted.* For Ex: IF you have a table with two columns one is ID and second is number and wants to find out the cumulative sum.
SELECT ID,Number,SUM(Number)OVER(ORDER BY ID) FROM T Once the table is created -
select A.id, A.SomeNumt, SUM(B.SomeNumt) as sum from @t A, @t B where A.id >= B.id group by A.id, A.SomeNumt order by A.id The SQL solution wich combines "ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW" and "SUM" did exactly what i wanted to achieve. Thank you so much!
If it can help anyone, here was my case. I wanted to cumulate +1 in a column whenever a maker is found as "Some Maker" (example). If not, no increment but show previous increment result.
So this piece of SQL:
SUM( CASE [rmaker] WHEN 'Some Maker' THEN 1 ELSE 0 END) OVER (PARTITION BY UserID ORDER BY UserID,[rrank] ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS Cumul_CNT Allowed me to get something like this:
User 1 Rank1 MakerA 0 User 1 Rank2 MakerB 0 User 1 Rank3 Some Maker 1 User 1 Rank4 Some Maker 2 User 1 Rank5 MakerC 2 User 1 Rank6 Some Maker 3 User 2 Rank1 MakerA 0 User 2 Rank2 SomeMaker 1 Explanation of above: It starts the count of "some maker" with 0, Some Maker is found and we do +1. For User 1, MakerC is found so we dont do +1 but instead vertical count of Some Maker is stuck to 2 until next row. Partitioning is by User so when we change user, cumulative count is back to zero.
I am at work, I dont want any merit on this answer, just say thank you and show my example in case someone is in the same situation. I was trying to combine SUM and PARTITION but the amazing syntax "ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW" completed the task.
Thanks! Groaker
Above (Pre-SQL12) we see examples like this:-
SELECT T1.id, SUM(T2.id) AS CumSum FROM #TMP T1 JOIN #TMP T2 ON T2.id < = T1.id GROUP BY T1.id More efficient...
SELECT T1.id, SUM(T2.id) + T1.id AS CumSum FROM #TMP T1 JOIN #TMP T2 ON T2.id < T1.id GROUP BY T1.id Try this
select t.id, t.SomeNumt, sum(t.SomeNumt) Over (Order by t.id asc Rows Between Unbounded Preceding and Current Row) as cum from @t t group by t.id, t.SomeNumt order by t.id asc; Try this:
CREATE TABLE #t( [name] varchar NULL, [val] [int] NULL, [ID] [int] NULL ) ON [PRIMARY] insert into #t (id,name,val) values (1,'A',10), (2,'B',20), (3,'C',30) select t1.id, t1.val, SUM(t2.val) as cumSum from #t t1 inner join #t t2 on t1.id >= t2.id group by t1.id, t1.val order by t1.id Without using any type of JOIN cumulative salary for a person fetch by using follow query:
SELECT * , ( SELECT SUM( salary ) FROM `abc` AS table1 WHERE table1.ID <= `abc`.ID AND table1.name = `abc`.Name ) AS cum FROM `abc` ORDER BY Name 
