SQL Add Running Total Column for each item in a giant table

Question

I wanna calculate a running total based on 5 types of transactions (let's say transaction A, B, C, D, E). But I have over one thousand different products in this table and each product could have millions of transaction records on different days.

So the table looks like this:

ProductID   A   B   C   D   E   Running Total

   1       10   0   5   0   5       20
   2       15   0   0   0   0       15
   3       20   5   0   10  0       35
   1       10   0   0   0   0       30 (20 for product 1, plus 10 for product 1 again)
   3       12   0   33  0   0       80 (35 for product 3, plus 45 for product 3 again)

What are you trying to accomplish? Do you need to calculate Running Total when inserting a new record? Do you control the code? Must you implement in SQL? Can you use a trigger or field procedure? — KingOfAllTrades, Sep 04 '14 at 20:36
What RDBMS? [MySQL](http://stackoverflow.com/questions/2563918/create-a-cumulative-sum-column-in-mysql)? [SQL SERVER](http://stackoverflow.com/questions/860966/calculate-a-running-total-in-sqlserver)? [oracle](http://stackoverflow.com/questions/439138/running-total-by-grouped-records-in-table)? The syntax varies for running totals. No matter which one, I'll bet I can find a running total example on this site for your RDBMS... window sets using `over` syntax is likely what you need in Oracle, DB2, SQL server, user variables in mySQL. — xQbert, Sep 04 '14 at 20:38
Hi guys thanks so much for your comments. I am using SQL Server 2008, and I am trying to calculate the running total of quantity for each product since a few years ago. Based on the running total, I could get the quantity on hand in our warehouse and monitor everything visually. I hope this would make the problem more clear. Thanks again I appreciate everybody's time and kindness. — user3775316, Sep 05 '14 at 13:19

score 1 · Answer 1 · answered Sep 04 '14 at 20:51

The ANSI standard method is to use sum() as a window function:

select t.*,
       sum(a + b + c + d + e) over (partition by productid order by <datetimecol>) as RunningTotal
from table t;

SQL tables represent unordered sets, so you need a column that specifies the ordering. I am guessing there is a date/time column somewhere for this purpose.

Most databases support this standard syntax: Oracle, SQL Server 2012+, Postgres, Teradata, and DB2.

M.Ali · Answer 2 · 2014-09-05T07:04:14.980

Test Data

DECLARE @TABLE TABLE (ProductID INT, A INT,  B INT,  C INT,  D INT,  E INT)  
INSERT INTO @TABLE VALUES
(1 ,10,   0,   5 ,  0 ,  5),  --     20
(2 ,15,   0,   0 ,  0 ,  0),  --     15
(3 ,20,   5,   0 ,  10,  0),  --     35
(1 ,10,   0,   0 ,  0 ,  0),  --     30 (20 for product 1, plus 10 for product 1 again)
(3 ,12,   0,   33,  0 ,  0)   --    80

Query

;WITH CTE AS
(
    select *
          ,ROW_NUMBER() OVER (PARTITION BY ProductID ORDER BY ProductID ASC) rn 
    from @TABLE  
)
SELECT ProductID 
      ,A 
      ,B
      ,C
      ,D
      ,E
      ,runningTotal
FROM CTE c
 cross apply (select sum(A+B+C+D+E) as runningTotal
                from CTE
                where rn <= c.rn
                  and ProductID = c.ProductID
              ) as rt

Result

╔═══════════╦════╦═══╦════╦════╦═══╦══════════════╗
║ ProductID ║ A  ║ B ║ C  ║ D  ║ E ║ runningTotal ║
╠═══════════╬════╬═══╬════╬════╬═══╬══════════════╣
║         1 ║ 10 ║ 0 ║  5 ║  0 ║ 5 ║           20 ║
║         1 ║ 10 ║ 0 ║  0 ║  0 ║ 0 ║           30 ║
║         2 ║ 15 ║ 0 ║  0 ║  0 ║ 0 ║           15 ║
║         3 ║ 20 ║ 5 ║  0 ║ 10 ║ 0 ║           35 ║
║         3 ║ 12 ║ 0 ║ 33 ║  0 ║ 0 ║           80 ║
╚═══════════╩════╩═══╩════╩════╩═══╩══════════════╝

SQL Add Running Total Column for each item in a giant table

2 Answers2

Test Data

Query

Result