hi guys, I have 2 tables, one called neworders which stores the part_id and num or parts and a 2nd table called parts which has all the part details including cost.
tables are as follows

part_id(primary key)

part_id(primary key)
7-8 more columns, eg size, desc, sup id

what i need to do is get the sum of the costs based on the quantity of the parts ordered

so if table is as follows

table new order
part_id number_of_parts
pt12     2
pt255   4

I need to use the part_id of the neworder table to get the costs of the parts and then add them up with sum based on number of parts ordered.

I have no idea how to do this, any help would be awesome

7 Years
Discussion Span
Last Post by mr_scooby
IF OBJECT_ID('tempdb..#NewOrders', 'U') IS NOT NULL DROP TABLE #NewOrders
IF OBJECT_ID('tempdb..#Parts', 'U') IS NOT NULL DROP TABLE #Parts
Create Table #NewOrders
  PartId varchar(10) PRIMARY KEY,
  number_of_parts int

Create Table #Parts
  PartId varchar(10) PRIMARY KEY,
  [Description] varchar(100),
  Cost money

Insert Into #Parts (PartId, Cost, [Description]) Values ('pt12', 5.00, 'A very cool part')
Insert Into #Parts (PartId, Cost, [Description]) Values ('2pt255', 7.22, 'A not so neat part')

Insert Into #NewOrders (PartId, number_of_parts) Values ('pt12', 500)
Insert Into #NewOrders (PartId, number_of_parts) Values ('2pt255', 900)

Select #NewOrders.PartId, #NewOrders.number_of_parts, #Parts.Cost, (#NewOrders.number_of_parts * #Parts.Cost) As TotalCost
From #NewOrders Inner Join #Parts On (#NewOrders.PartId = #Parts.PartId)

Results in:

PartId     number_of_parts Cost                  TotalCost
---------- --------------- --------------------- ---------------------
2pt255     900             7.22                  6498.00
pt12       500             5.00                  2500.00
This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.