CODEWITHSUNDEEP
sql-serversql-server-2008t-sql
FFMT88
FFMT88

Reputation: 5

TSQL - Creating a running total of a column

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:

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

Upvotes: 0

Views: 96

Answers (3)

Pரதீப்
Pரதீப்

Reputation: 93694

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

Upvotes: 0

M.Ali
M.Ali

Reputation: 69494

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 ║
╚═══════╩═════╩══════════════╝

Upvotes: 0

sgeddes
sgeddes

Reputation: 62831

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

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.

Upvotes: 1

Related Questions