Ad:

MS SQL Discussion Thread
Marked Solved
Views: 699

Oct 27th, 2009

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

Expand Post »

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

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

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

Similar Threads

Collect sum of a column in a html table? in JavaScript / DHTML / AJAX
Sum all the values in one colum of a html table? in PHP
Copy records from one table to samme table in another sql server in MS SQL
Select SUM (All Fields in table) in MySQL
Trying to find character occurrences in table field using SUM??? in MySQL

mr_scooby

Reputation Points: 10

Solved Threads: 0

Junior Poster in Training

Offline

58 posts
since Feb 2008

Permalink

Oct 27th, 2009

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

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:

text Syntax (Toggle Plain Text)

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

sknake

Featured Poster

Reputation Points: 1749

Solved Threads: 735

Senior Poster

Offline

3,948 posts
since Feb 2009

Permalink

Oct 27th, 2009

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

that is awesome thanks dude.

mr_scooby

Reputation Points: 10

Solved Threads: 0

Junior Poster in Training

Offline

58 posts
since Feb 2008

This thread is solved

Either the thread starter or a moderator has marked this thread as solved. You can most likely trust the responses and answers given. There is most likely no reason for any further responses to be posted here. If you have a related question, please start a new thread in this forum instead.

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in MS SQL Forum Timeline: UPDATE with INNER JOIN

Next Thread in MS SQL Forum Timeline: Loop to generate future dates?!

DaniWeb Message

Cancel Changes

User Name
Password