SQL Server Cumulative Sum by Group

cumulative-sum, sql, sql-server

Solution

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;

Problem

I have a table (SQL Server 2005) of this format: dummy_id, date_registered, item_id, quantity, price and I want to add a new column (cumulative) which calculates the cumulative totals of each item_id order by date_registered as shown: ``` dummy_id date_registered item_id quantity price cumulative 1 2013-07-01 100 10 34.5 10 2 2013-07-01 145 8 2.3 8 3 2013-07-11 100 20 34.5 30 4 2013-07-23 100 15 34.5 45 5 2013-07-24 145 10 34.5 18 ``` Thanx in advance

Original source