0

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

Improve this question
5
  • Do you have a date, id or some other field to define the order to sum the results? One option is to use a correlated subquery... Commented Dec 29, 2014 at 20:51
  • What column represents the order of rows? What version of SQL Server? What do the desired results look like? Commented Dec 29, 2014 at 20:52
  • Sorry, the "row x:" I put there just for reference. It will be just three columns: Part_No, Quantity, Total_Quantity. SQL 2008. Commented Dec 29, 2014 at 20:56
  • Then how do you want the running totals to be calculated? I mean in order of what column? Commented Dec 29, 2014 at 20:57
  • I am just ordering it by part number right now. So for example: part1|55|55, (second row):part1|105|160, (third row):part1|70|230 Commented Dec 29, 2014 at 20:59

3 Answers 3

Reset to default
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

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

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

1 Comment

Doesn't work as expected if two rows for the same part have the same quantity.
0

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
Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.