January 13, 2012 at 1:20 pm
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.
January 13, 2012 at 1:56 pm
selectoi.OrderItemsID
,oi.ItemID
,oi.OrderID
,oc.OrderDescription
,TotalCharge=Sum(oc.OrderCharge)
from#OrderItems oi
left outer join #OrderCharges oc on oc.OrderID = oi.OrderID
and oc.itemid = oi.itemid
group by oi.OrderItemsID
,oi.ItemID
,oi.OrderID
,oc.OrderDescription
January 13, 2012 at 2:10 pm
Burninator,
This was exactly what I was looking for. Thank you!
January 13, 2012 at 2:22 pm
Glad to help!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply