SQL Help - query to find a weighted average

[NightZEN]

I need to calculate a weighted average cost from Purchase Order History. this is what I want

For each part number and PO, mulliply the qty by the cost to get a total.

Then sum all purchases together, and divide by the total purchased qty. Pretty straight forward in theory... so if i have this data:

PARTNUM QTY PRICE

123 10 $1

123 20 $.5

123 15 $ 1.5

My weighted average Calculation would look like this: W-AVERAGE = ( (10 * 1) + (20 * .5) + (15 * 1.5) ) / (10 + 20 + 15) = .9444

I want the query to return just PARTNUM and W-AVERAGE. No details.

So, how can I do this with SQL? I have done simple grouping with Sums and Averages, but as you can see I need a weighted calculation based on purchased quantity. The straight average ignoring qty is $1, but when you "weight" it, it is $.9444. Do i need a subquery in my FROM clause?

Any tips or ideas would be great!



Thanks, kw

 

[randy700]

SELECT PartNum, Sum([Qty]*[Price]) AS Amount, Sum([Qty]) AS Total, [Amount]/[Total] AS WAverage
FROM tblParts
GROUP BY PartNum;

Randy

 

[PHV]

query to return just PARTNUM and W-AVERAGE
SELECT PARTNUM, Sum(QTY*PRICE)/Sum(QTY) AS [W-AVERAGE]
FROM yourTable
GROUP BY PARTNUM

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886