Weighted Average Cost In MySql

[KenBZim]

Using VB.NET (VS 2008) and Mysql Database

I have an inventory items table that records inventory items in and out
what i am looking for is a query that will return the current weighted average cost
at any particular moment in time.

A simple View of the table looks something like this:


Part Code Qty Price
ABC001 10 5.50
ABC001 5 5.32
ABC001 -3 5.44
ABC001 20 5.65


Current Weighted Average = 5.57125

what i have at the moment is a query that sums the quantities, and the (quantities * the price) and then divides
the total of (Qty * Price) by the total Quantities on hand at the time of the query.

Part Code Qty Price
ABC001 10 5.50 55.00
ABC001 5 5.32 26.60
ABC001 -3 5.44 -16.32
ABC001 20 5.65 113.00

Totals 32 178.28

Therefore to calculate the current weighted average cost
178.28 / 32 =5.57125

to Achieve this i have a view that looks like this:

select
`inventory_new`.`InventCode` AS `code`,
sum(`doc_transactions`.`Quantity`) AS `qty`,
sum((`doc_transactions`.`UnitPrice` * `doc_transactions`.`Quantity`)) AS `value`
from inventory_new

To obtain the average price i now run a query against this view that divides the value by the quantity.
This works fine provided there is stock on hand at the time of the query, if there is no stock on hand
then the query returns a weighted average cost of zero
My question: is there a better way of determining the weighted average cost?

[smendoza]

I'm going to guess and say you have some NULL values in the database, in that case you will want to use the COALESCE() function. The coalesce function returns whatever value you tell it to, for example if the qty is NULL you would want it to return the number 0 so you can do you calculations

select SUM(COALESCE(QTY, 0)) as qty
from SomeTable