August 17, 2021 at 3:41 pm
there are LineNo, itemNo and qty fields in one table, I want to the accumulative qty (totalQty) for each item and want to the totalqty shown as below picture, I just know one way to get the the result , could you please help me how many ways to get the result, it seems that there is a new method to show the accumulative value in the detailed record, but I don't know ... thank you!
August 17, 2021 at 4:43 pm
Try googling "tsql running total" and you will see a number of tutorials about how to do that.
August 17, 2021 at 4:48 pm
If you are hoping for a coded solution, please take the time to provide your sample data in a form which we can paste into SSMS and execute.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
August 17, 2021 at 6:13 pm
use a windowing function with SUM and UNBOUNDED PRECEDING.
August 17, 2021 at 9:02 pm
there are LineNo, itemNo and qty fields in one table, I want to the accumulative qty (totalQty) for each item and want to the totalqty shown as below picture, I just know one way to get the the result , could you please help me how many ways to get the result, it seems that there is a new method to show the accumulative value in the detailed record, but I don't know ... thank you!
Dude. You've been around long enough to know that you really need to post readily consumable data so people can build some actual code for you.
Pietlinden gave you the correct worded answer but that's happened many times in the past with you and the next question off your keyboard is normally something to the effect of "Thanks. Can you show me how to use it"? If you'd take the minute or two to post the data as readily consumable data as you're been asked to do so many times, you'd have the real answer by now.
You've also been told that pretty pictures don't help at all except for desired results.
Actually, kaj has the most correct answer so far.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 19, 2021 at 3:35 pm
892717952 wrote:there are LineNo, itemNo and qty fields in one table, I want to the accumulative qty (totalQty) for each item and want to the totalqty shown as below picture, I just know one way to get the the result , could you please help me how many ways to get the result, it seems that there is a new method to show the accumulative value in the detailed record, but I don't know ... thank you!
Dude. You've been around long enough to know that you really need to post readily consumable data so people can build some actual code for you.
Pietlinden gave you the correct worded answer but that's happened many times in the past with you and the next question off your keyboard is normally something to the effect of "Thanks. Can you show me how to use it"? If you'd take the minute or two to post the data as readily consumable data as you're been asked to do so many times, you'd have the real answer by now.
You've also been told that pretty pictures don't help at all except for desired results.
Actually, kaj has the most correct answer so far.
Sorry! next time if have the same question I'll provide sample data, thanks!
August 19, 2021 at 7:31 pm
Sorry! next time if have the same question I'll provide sample data, thanks!
So, you're happy with the answers you got on this thread?
--Jeff Moden
Change is inevitable... Change for the better is not.
August 27, 2021 at 3:11 pm
August 27, 2021 at 3:46 pm
FWIW, here's the worked solution, so folks at home can play along...
use tempdb;
go
/* setup script */CREATE TABLE LineItem(
LineNum int not null,
ItemNo char not null,
Qty tinyint not null);
GO
INSERT INTO LineItem VALUES (1,'A',10),(2,'A',15),(3,'A',20),(1,'B',5),(2,'B',16),(1,'C',100),(2,'C',10),(3,'C',20),(4,'C',20);
/* solution */select LineNum,
ItemNo,
Qty,
RTQty = SUM(Qty) OVER (PARTITION BY ItemNo ORDER BY LineNum
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
FROM LineItem
ORDER BY ItemNo, LineNum;
August 30, 2021 at 6:08 am
FWIW, here's the worked solution, so folks at home can play along...
use tempdb;
go
/* setup script */CREATE TABLE LineItem(
LineNum int not null,
ItemNo char not null,
Qty tinyint not null);
GO
INSERT INTO LineItem VALUES (1,'A',10),(2,'A',15),(3,'A',20),(1,'B',5),(2,'B',16),(1,'C',100),(2,'C',10),(3,'C',20),(4,'C',20);
/* solution */select LineNum,
ItemNo,
Qty,
RTQty = SUM(Qty) OVER (PARTITION BY ItemNo ORDER BY LineNum
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
FROM LineItem
ORDER BY ItemNo, LineNum;
thank you!
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply