SQL Server Cumulative Sum by Group

by

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;

More Related Contents:

  1. Calculate a Running Total in SQL Server
  2. Syntax not valid on word ‘ON’ in SQL join statement [closed]
  3. Can anyone help me to get the query to retrieve data like below [closed]
  4. Need SQL Script
  5. Optimal way to concatenate/aggregate strings
  6. Maximum size for a SQL Server Query? IN clause? Is there a Better Approach [duplicate]
  7. SQL JOIN vs IN performance?
  8. Convert varchar into datetime in SQL Server
  9. Can a foreign key be NULL and/or duplicate?
  10. TSQL Pivot without aggregate function
  11. How to generate a range of dates in SQL Server
  12. Fastest way to perform nested bulk inserts with scope_identity() usage?
  13. SQL RANK() versus ROW_NUMBER()
  14. Why are there gaps in my IDENTITY column values?
  15. Drop all the tables, stored procedures, triggers, constraints and all the dependencies in one sql statement
  16. Insert Picture into SQL Server 2005 Image Field using only SQL
  17. Execute Stored Procedure from a Function
  18. How do I use ROW_NUMBER()?
  19. Search for “whole word match” with SQL Server LIKE pattern
  20. Why are logical reads for windowed aggregate functions so high?
  21. Why is SQL server throwing this error: Cannot insert the value NULL into column ‘id’?
  22. How to sum up time field in SQL Server
  23. Select corresponding to row from the same table SQL Server
  24. How do I use alias in where clause? [duplicate]
  25. Group All Related Records in Many to Many Relationship, SQL graph connected components
  26. Should I use the CASCADE DELETE rule? [duplicate]
  27. Dynamic order direction
  28. Why is RAND() not producing random numbers?
  29. SQL, How to Concatenate results?
  30. Should every SQL Server foreign key have a matching index? [closed]

Leave a Comment