MS SQL

MS SQL RSS

Getting sum of cost from one table where quantity on another table

Please support our MS SQL advertiser: Intel Parallel Studio Home

Thread Solved

•

Join Date: Feb 2008

Posts: 35

Reputation: mr_scooby is an unknown quantity at this point

Solved Threads: 0

mr_scooby

Offline

Light Poster

Getting sum of cost from one table where quantity on another table

32 Days Ago

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

 Help with Code Tags 
 MS SQL Syntax (Toggle Plain Text) 
neworder 
part_id(PRIMARY KEY)
number_of_parts
 
parts
part_id(PRIMARY KEY)
7-8 more COLUMNS, eg size, DESC, sup id
cost
neworder 
part_id(primary key)
number_of_parts

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

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

 Help with Code Tags 
 MS SQL Syntax (Toggle Plain Text) 
TABLE new ORDER
part_id number_of_parts
pt12     2
pt255   4
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

•

Join Date: Feb 2009

Posts: 3,210

Reputation: sknake has much to be proud of

Solved Threads: 572

sknake

Offline

.NET Enthusiast

32 Days Ago

 Help with Code Tags 
 MS SQL Syntax (Toggle Plain Text) 
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)
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:

Help with Code Tags

text Syntax (Toggle Plain Text)

PartId     number_of_parts Cost                  TotalCost
---------- --------------- --------------------- ---------------------
2pt255     900             7.22                  6498.00
pt12       500             5.00                  2500.00

Scott Knake
Custom Software Development
Apex Software, Inc.