Query that joins two tables and retrieves a sum

Hammerklavier · #1 (**permalink**) 01-13-12, 13:57

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:

Quote:

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.

Wim · #2 (**permalink**) 01-13-12, 15:46

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

Hammerklavier · #3 (**permalink**) 01-13-12, 16:07

Wim,

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

Internet Services

Web Development

Digital Marketing

Consumer Tech