Post reply

Creating a sum column without losing records

  • December 20, 2011 at 3:11 pm

    #247964

    Hi,

    I have the following table:

    --===== If the test table already exists, drop it

    IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL

    DROP TABLE #orders

    --===== Create the test table with

    CREATE TABLE #orders

    (

    ID INT IDENTITY (1,1) PRIMARY KEY,

    OrderNumber INT,

    Total DECIMAL(8, 2)

    )

    SET IDENTITY_INSERT #orders ON

    INSERT INTO #orders

    (ID, OrderNumber, Total)

    SELECT '1', '463', 2.50 UNION ALL

    SELECT '2', '463', 1.00 UNION ALL

    SELECT '3', '542', 2.00 UNION ALL

    SELECT '4', '618', 5.50 UNION ALL

    SELECT '5', '618', 1.15 UNION ALL

    SELECT '6', '618', 3.75

    select * from #orders

    I am looking to create a sum column that will give me the sum for each sales order. So it would look something like this...

    ID OrderNumber Total OrderTotal

    14632.50 3.50

    24631.00 3.50

    35422.00 2.00

    46185.50 10.40

    56181.15 10.40

    66183.75 10.40

    If this is pretty elementary, I apologize, but I'm not sure how to pull it off. The idea is to keep each individual record and still display the total for that order.

    Thank you

  • December 20, 2011 at 3:34 pm

    #1423943

    There may be a better way to do this but the below query works.

    select o.ID,o.Ordernumber,o.Total,ot.OrderTotal

    from #orders o

    inner join (select sum(total) as OrderTotal,ordernumber from #orders group by ordernumber) ot on o.ordernumber = ot.ordernumber

  • December 20, 2011 at 3:37 pm

    #1423944

    Skinning cat method #2:

    select * from #orders o

    cross apply (

    select OrderNumber, SUM(total) OrderTotal

    from #orders

    where OrderNumber = o.OrderNumber

    group by OrderNumber) a

    ______________________________________________________________________________
    How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.

  • December 20, 2011 at 3:42 pm

    #1423945

    Is that the same thing? Does the cross apply act as a inner join because of where OrderNumber = o.OrderNumber?

  • December 21, 2011 at 1:05 pm

    #1424427

    Thank you. This was definitely helpful.

  • December 21, 2011 at 2:31 pm

    #1424473

    bwoulfe (12/20/2011)

    Is that the same thing? Does the cross apply act as a inner join because of where OrderNumber = o.OrderNumber?

    Don't think of it as a join but instead as a function applied to the initial data set. Very powerful tool to add to your arsenal.

    http://technet.microsoft.com/en-us/library/ms175156.aspx

    ______________________________________________________________________________
    How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.

  • December 21, 2011 at 2:57 pm

    #1424480

    I'm familiar with the apply operator. I'll admit I don't use it too often though. I was just asking if the apply acts as an inner join in your example? I think it does...just wondering though.

  • December 21, 2011 at 3:04 pm

    #1424484

    bwoulfe (12/21/2011)

    I'm familiar with the apply operator. I'll admit I don't use it too often though. I was just asking if the apply acts as an inner join in your example? I think it does...just wondering though.

    Cross Apply acts like Inner Join but there is Outer Apply also, which acts like Left Outer Join.

  • December 21, 2011 at 3:06 pm

    #1424487

    Dev (12/21/2011)

    bwoulfe (12/21/2011)

    I'm familiar with the apply operator. I'll admit I don't use it too often though. I was just asking if the apply acts as an inner join in your example? I think it does...just wondering though.

    Cross Apply acts like Inner Join but there is Outer Apply also, which acts like Left Outer Join.

    That's more along the lines of what I was getting at....just trying to figure out the benefit in using method 2.

    Thanks...sorry I went off on a tangent here...lol

  • December 21, 2011 at 3:21 pm

    #1424492

    bwoulfe (12/21/2011)

    Dev (12/21/2011)

    bwoulfe (12/21/2011)

    I'm familiar with the apply operator. I'll admit I don't use it too often though. I was just asking if the apply acts as an inner join in your example? I think it does...just wondering though.

    Cross Apply acts like Inner Join but there is Outer Apply also, which acts like Left Outer Join.

    That's more along the lines of what I was getting at....just trying to figure out the benefit in using method 2.

    Thanks...sorry I went off on a tangent here...lol

    Cross Apply is typically used with Table Valued Functions. Normal Joins won't work there.

    Paul has written a nice article on that. Please search for it on SSC.

  • December 21, 2011 at 3:23 pm

    #1424493

    I will take a look. Thanks.

  • December 21, 2011 at 9:11 pm

    #1424557

    CROSS APPLY is certainly a marvel... but you don't need it here.

    SELECT ID, OrderNumber, Total,

    OrderTotal = SUM(Total) OVER (PARTITION BY OrderNumber)

    FROM #Orders;

    --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)

  • December 22, 2011 at 7:40 am

    #1424793

    Awesome. Thanks Jeff, everytime you post, I learn something. Next lesson for me: windowing functions.

    ______________________________________________________________________________
    How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.

  • December 22, 2011 at 9:33 am

    #1424856

    Thanks for the great feedback, Todd. 🙂

    --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)

Viewing 14 posts - 1 through 13 (of 13 total)

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