SQL Server Cumulative Sum by Group

SQL Server Cumulative Sum by Group

In SQL Server 2005, I would do this using a correlated subquery:

select dummy_id, date_registered, item_id, quantity, price,
       (select sum(quantity)
        from t t2
        where t2.item_id = t.item_id and
              t2.date_registered <= t.date_registered
       ) as cumulative
from table t;

If you actually want to add this into a table, you need to alter the table to add the column and then do an update. If the table has inserts and updates, you will need to add a trigger to keep it up-to-date. Getting it through a query is definitely easier.

In SQL Server 2012, you can do this using the syntax:

select dummy_id, date_registered, item_id, quantity, price,
       sum(quantity) over (partition by item_id order by date_registered) as cumulative
from table t;

SQL Cumulative Sum by Group by time condition

If you want cumulative quantities for the current month -- which is what I suspect you want, then change the partition by:

select dummy_id, date_registered, item_id, quantity, price,
       sum(quantity) over (partition by item_id, date_trunc('month', date_registered) order by date_registered) as cumulative
from table t;

If you really want the last month, then use a range window frame with interval:

select dummy_id, date_registered, item_id, quantity, price,
       sum(quantity) over (partition by item_id
                           order by date_registered
                           range between interval '1 month' preceding and current row
                          ) as cumulative
from table t;

The first seems much more useful to me.

Running total by grouped records in table

Do you really need the extra table?

You can get that data you need with a simple query, which you can obviously create as a view if you want it to appear like a table.

This will get you the data you are looking for:

select 
    account, bookdate, amount, 
    sum(amount) over (partition by account order by bookdate) running_total
from t
/

This will create a view to show you the data as if it were a table:

create or replace view t2
as
select 
    account, bookdate, amount, 
    sum(amount) over (partition by account order by bookdate) running_total 
from t
/

If you really need the table, do you mean that you need it constantly updated? or just a one off? Obviously if it's a one off you can just "create table as select" using the above query.

Test data I used is:

create table t(account number, bookdate date, amount number);

insert into t(account, bookdate, amount) values (1, to_date('20080101', 'yyyymmdd'), 100);

insert into t(account, bookdate, amount) values (1, to_date('20080102', 'yyyymmdd'), 101);

insert into t(account, bookdate, amount) values (1, to_date('20080103', 'yyyymmdd'), -200);

insert into t(account, bookdate, amount) values (2, to_date('20080102', 'yyyymmdd'), 200);

commit;

edit:

forgot to add; you specified that you wanted the table to be ordered - this doesn't really make sense, and makes me think that you really mean that you wanted the query/view - ordering is a result of the query you execute, not something that's inherant in the table (ignoring Index Organised Tables and the like).

Running total per group in SQL Server 2008

The best way to do this is with cumulative sum. Perhaps it is time to update to a more recent version of SQL Server?

In SQL Server 2008, you can use apply or a correlated subquery:

Select [Group], [Day], sum(money) as [Money],
       (select sum(t2.money)
        from table_name t2
        where t.group = t2.group and t2.day <= t.day
       ) as Column4
From Table_Name t
Group by [Group], [Day];

Cumulative sum and cumulative count in sql

We can use window functions in SQL whenever we want to show group-level aggregations in raw data itself.

select *, max(course_Count) over (partition by Coursenum order by Session_date) as Total_Session 
from( select *, count(*) over (partition by Coursenum order by Session_date) as course_Count 
from( select *,sum(case when courses = 'start' then 1 else 0 end) over (order by Session_date) as Coursenum
from [table_name]
) A)

Refer this link for more information on window functions
and this for Case statement

Reset cumulative sum column after threshold with groups

Capping a cumulative SUM by using standard SUM() OVER() is not possible due to threshold. One way to achieve such result is recursive CTE:

WITH cte_r AS (
  SELECT t.*, ROW_NUMBER() OVER(PARTITION BY GroupNr ORDER BY (SELECT 1)) AS rn 
  FROM Table1 t
), cte AS (
  SELECT GroupNr, Name, [Sum], [CumSum],
        CAST([Sum] AS INT) AS ResetCumSum,
        rn
  FROM cte_r
  WHERE rn = 1
  UNION ALL
  SELECT cte_r.GroupNr, cte_r.Name, cte_r.[Sum], cte_r.[CumSum],
  CAST(CASE WHEN cte.ResetCumSum >= 330 THEN 0 ELSE cte.ResetCumSum END + cte_r.[Sum] AS INT) 
       AS ResetCumSum,
       cte_r.rn
  FROM cte
  JOIN cte_r
    ON cte.rn = cte_r.rn-1
   AND cte.GroupNr = cte_r.GroupNr
)
SELECT GroupNr, Name, [Sum], [CumSum], ResetCumSum
FROM cte
ORDER BY GroupNr, rn;

Output:

db<>fiddle demo

Warning: Table by design is unordered set so to get stable result a order column is required(like unqiue id, timestamp). Here to emulate insert ROW_NUMBER() OVER(PARTITION BY GroupNr ORDER BY (SELECT 1)) AS rn was used but it is not stable.

Related:

Conditional SUM and the same using MATCH_RECOGNIZE - in my opinion the cleanest way

---

Extra:

Quirky UPDATE: Running Total until specific condition is true

Disclaimer: "DO NOT USE IT AT PRODUCTION!!!"

-- source table to be extended with id and Resetcumsum  columns
CREATE CLUSTERED INDEX IX_ROW_NUM  ON Table1(GroupNr, id);

DECLARE @running_total       NUMERIC(14,2) = 0
        ,@prev_running_total NUMERIC(14,2) = 0
        ,@prev_GroupNr       INT = 0;

UPDATE Table1
SET 
   @prev_running_total = @running_total
  ,@running_total = Resetcumsum = IIF(@prev_GroupNr != GroupNr 
                                      OR @running_total >= 330, 0, @running_total) 
                                    + [Sum]
  ,@prev_GroupNr = GroupNr
FROM Table1 WITH(INDEX(IX_ROW_NUM))
OPTION (MAXDOP 1);

SELECT *
FROM Table1
ORDER BY id;

db<>fiddle demo - 2

How to get cumulative sum by group

You were not so far, but I've try with less complicated queries :

DECLARE @t table(
        id int
        )

INSERT  INTO @t (id)
VALUES  (0),(1),(2),(3),(4),(5),(6),
        (7),(8),(9),(10),(11),(12),(13),
        (14),(15),(16),(17),(18),(19),
        (20),(21),(22),(23),(24),(99);

SELECT  *
        ,SUM(id)
         OVER(ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS Cum_STEPID
FROM    @t
WHERE   id < 14
UNION
SELECT  *
        ,SUM(CASE WHEN id = 14 THEN 91000 ELSE id END)
         OVER(ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS Cum_STEPID
FROM    @t
WHERE   id >= 14

---

Related Topics