TSQL - Creating a running total of a column

Question

I am writing a production record report and am having some difficulty. Here is what I am trying to accomplish:

Let's say I am creating a temp table with 3 columns:

• Table: #TProductionRecords

• column #1: Part_No

• column #2: Quantity
• column #3: Total_Quantity

Any given part may be produced multiple times on any given day, in different quantities:

row 1:  part1  55 
row 2:  part1  105
row 3:  part1  70 
row 4:  part2  100
row 5:  part2  25
row 6:  part3  150
row 7:  part3  50
row 8:  part3  35
row 9:  part3  80

etc..

I would like the Total_Quantity column to start a running total, then reset when there is a new part. I have the select query already, but I just do not know how to add in the Total_Quantity column.

Any help would be great!

Thanks

Answer 1

Without knowing the order of the results, this uses a correlated subquery and a common-table-expression which orders the records randomly with row_number:

with cte as (
  select *, 
    row_number() over (partition by part_no order by (select null)) rn
  from yourtable
  )
select part_no,
  quantity,
  (select sum(quantity)
   from cte c2
   where c2.rn <= c1.rn and c1.part_no = c2.part_no) total_qty
from cte c1
order by c1.part_no, c1.rn

• SQL Fiddle Demo

As per several of the comments, the question about the order is important. SQL Server does not guarantee an order of the results when they are returned. So a running total could start with Part1/55 sometimes, and perhaps Part1/105 other times -- lots of determining factors. You're best to define an order to return your results.

Answer 2

Test Data

DECLARE @Table TABLE ( Part VARCHAR(100), Qty INT)
INSERT INTO @Table VALUES 
('part1',  55), 
('part1',  105),
('part1',  70 ),
('part2',  100),
('part2',  25),
('part3',  150),
('part3',  50),
('part3',  35),
('part3',  80)

Query

SELECT t.* , ISNULL(C.RunningTotal, 0) + t.Qty AS RunningTotal
FROM @Table t
    CROSS APPLY (SELECT SUM(Qty)
                 FROM @Table 
                 WHERE t.Part = Part
                  AND t.Qty > Qty)c(RunningTotal)
ORDER BY t.Part, t.Qty

Result

╔═══════╦═════╦══════════════╗
║ Part  ║ Qty ║ RunningTotal ║
╠═══════╬═════╬══════════════╣
║ part1 ║  55 ║           55 ║
║ part1 ║  70 ║          125 ║
║ part1 ║ 105 ║          230 ║
║ part2 ║  25 ║           25 ║
║ part2 ║ 100 ║          125 ║
║ part3 ║  35 ║           35 ║
║ part3 ║  50 ║           85 ║
║ part3 ║  80 ║          165 ║
║ part3 ║ 150 ║          315 ║
╚═══════╩═════╩══════════════╝

Answer 3

In addition to the above answers Running totals can be calculated in following methodds also

Method 1: Correlated Subquery

SELECT *,
       (SELECT Sum(Qty)
        FROM   @Table t
        WHERE  t.Part = a.Part
               AND t.Qty <= a.Qty)
FROM   @Table a
ORDER  BY a.Part,a.Qty

Method 2: Self Join

SELECT a.Part,
       a.Qty,
       Sum(b.qty)
FROM   @Table A
       JOIN @Table b
         ON a.Part = b.Part
            AND a.Qty >= b.Qty
GROUP  BY a.Part,a.Qty
ORDER  BY a.Part,a.Qty