How to get cumulative sum

Question

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

User · Answer

There is a much faster CTE implementation available in this excellent post: http://weblogs.sqlteam.com/mladenp/archive/2009/07/28/SQL-Server-2005-Fast-Running-Totals.aspx

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.*

User · Answer

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

User · Answer

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  amp  OVER Clause when analyzed the actual query plan         Create table  amp  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  gt   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  gt    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

User · Answer

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  gt   t2 id  group by t1 id  t1 val order by t1 id

User · Answer

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

User · Answer

Lets first create a table with dummy data --   Create Table CUMULATIVESUM  id tinyint   SomeValue tinyint     Now let put some data in 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  gt   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 ans    Select c1 ID  c1 SomeValue  Sum c2 SomeValue  CumulativeSumValue From CumulativeSum c1   CumulativeSum c2 Where c1 id  gt   c2 ID Group By c1 ID  c1 SomeValue Order By c1 id Asc      FOR SQL SERVER 2012 and above Much better perform    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   Clear the dummytable

User · Answer

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

User · Answer

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  lt    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  lt  T1 id GROUP BY     T1 id

User · Answer

Select     Select SUM SOMENUMT               From  t S              Where S id  lt   M id  From  t M

User · Answer

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

User · Answer

select t1 id  t1 SomeNumt  SUM t2 SomeNumt  as sum from  t t1 inner join  t t2 on t1 id  gt   t2 id group by t1 id  t1 SomeNumt order by t1 id  SQL Fiddle example 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

User · Answer

The 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

User · Answer

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  lt     abc  ID     AND table1 name     abc  Name   AS cum FROM   abc   ORDER BY Name

User · Answer

Once the table is created -   select      A id  A SomeNumt  SUM B SomeNumt  as sum     from  t A   t B where A id  gt   B id     group by A id  A SomeNumt  order by A id

User · Answer

For 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 https   msdn microsoft com en-us library ms189461 aspx

User · Answer

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

[sql-server] How to get cumulative sum

Examples related to sql-server

Examples related to sql-server-2008