How to get the detailed record and summary value at the same time

  • 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!

    Attachments:
    You must be logged in to view attached files.
  • Try googling "tsql running total" and you will see a number of tutorials about how to do that.

     

  • 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 aren't happy single, you won't be happy in a relationship.

    Remember, happiness comes from guitars, not relationships.

  • use a windowing function with SUM and UNBOUNDED PRECEDING.

  • 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.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • kaj wrote:

    Try googling "tsql running total" and you will see a number of tutorials about how to do that.

    big thanks !

     

  • Phil Parkin wrote:

    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.

    OK, next time I will provide sample data if have similar question, thank you!

  • Jeff Moden wrote:

    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!

     

  • 892717952 wrote:

    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


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • Jeff Moden wrote:

    892717952 wrote:

    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?

    Yes, I have learnt the answer of my question and also how to handle the simliar post in future, thanks!

     

  • 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;

    • This reply was modified 3 months, 1 week ago by  pietlinden.
  • pietlinden wrote:

    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 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply