Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2011
    Posts
    11

    Unanswered: Query that joins two tables and retrieves a sum (resolved)

    Hi,

    I want to join two tables of information together. I want to bring the order information together with the order charge. But since the OrderCharges table can have multiple values, I just want to retrieve the sum of those charges for a specific OrderID+ItemID.

    My data looks something like this:

    CREATE TABLE #OrderItems
    (
    OrderItemsID INT IDENTITY (1,1) PRIMARY KEY,
    OrderID INT,
    ItemID INT
    )

    SET IDENTITY_INSERT #OrderItems ON

    INSERT INTO #OrderItems
    (OrderItemsID, ItemID, OrderID)
    SELECT '1', '124', '1' UNION ALL
    SELECT '2', '156', '1' UNION ALL
    SELECT '3', '156', '2' UNION ALL
    SELECT '4', '158', '1' UNION ALL
    SELECT '5', '158', '2'

    SET IDENTITY_INSERT #OrderItems OFF

    CREATE TABLE #OrderCharges
    (
    OrderChargesID INT IDENTITY (1,1) PRIMARY KEY,
    OrderID INT,
    ItemID INT,
    OrderDescription VARCHAR (32),
    OrderCharge MONEY
    )

    SET IDENTITY_INSERT #OrderCharges ON

    INSERT INTO #OrderCharges
    (OrderChargesID, ItemID, OrderID, OrderDescription, OrderCharge)
    SELECT 1, 124, 1, 'Shipping', 6.55 UNION ALL
    SELECT 2, 156, 1, 'Shipping', 8.30 UNION ALL
    SELECT 3, 156, 1, 'Shipping', 8.55 UNION ALL
    SELECT 4, 156, 2, 'Shipping', 7.40 UNION ALL
    SELECT 5, 158, 1, 'Shipping', 7.85 UNION ALL
    SELECT 6, 158, 2, 'Shipping', 7.40 UNION ALL
    SELECT 7, 158, 2, 'Shipping', 7.85

    SET IDENTITY_INSERT #OrderCharges OFF

    select * from #OrderItems
    select * from #OrderCharges
    So I'm looking to see a result set that would like this this:

    OrderItemsID-----ItemID-----OrderID----OrderDescription--OrderCharge
    ----1------------124---------1------------Shipping----------6.55
    ----2------------156---------1------------Shipping---------16.85
    ----3------------156---------2------------Shipping----------7.40
    ----4------------158---------1------------Shipping----------7.85
    ----5------------158---------2------------Shipping---------15.25

    Please let me know if there are any questions. I greatly appreciate any help offered on this.
    Last edited by Hammerklavier; 01-13-12 at 17:09.

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Try this:
    Code:
    SELECT OI.OrderItemsID, OI.ItemID, OI.OrderID, T.OrderDescription, T.OrderCharge
    FROM #OrderItems as OI
    	INNER JOIN 	(SELECT OC.OrderID, OC.ItemID, OC.OrderDescription, SUM(OC.OrderCharge) as OrderCharge
    		FROM #OrderCharges as OC
    		GROUP BY OC.OrderID, OC.ItemID, OC.OrderDescription
    		) as T ON
    		OI.OrderID = T.OrderID AND
    		OI.ItemID = T.ItemID
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  3. #3
    Join Date
    Nov 2011
    Posts
    11
    Wim,

    Thank you! This was exactly what I was looking for.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •