# Weighted Average Calculations in TSQL

### Question

• Hi,
I wish to calculate a weighted average cost on a countback FIFO basis (First IN First Out) of my stock using T-SQL. I have a list of 1600 distinct parts who all have varying amounts of stock. For Example:

Current Stock 15,000 as at 16/08/09

Part             DeliveredQty           DeliveryDate             Unitprice
18-980012       3000                       15/01/09               £0.18
18-980012       4000                       15/02/09               £0.42
18-980012       2800                       15/04/09               £0.29
18-980012       1700                       15/06/09               £0.86
18-980012       1750                       15/07/09               £1.12
18-980012       1750                       15/08/09               £1.24

Mofi
Wednesday, September 16, 2009 11:08 AM

• ```select PART, '£'+convert(varchar(20),sum(deliveredqty*unitprice)/SUM(deliveredqty)) as AveragePerItem from #m
group by part```

PART           AveragePerItem
18-980012   £0.57

Best Regards,

If you have found this post helpful, please click the 'Vote as Helpful ' link (the green triangle and number on the top-left).

If this post answers your question, click the 'Mark As Answered ' link below. It helps others who experience the same issue in future to find the solution.

• Proposed as answer by Friday, September 18, 2009 7:50 AM
• Marked as answer by Tuesday, September 22, 2009 6:13 AM
Friday, September 18, 2009 6:57 AM

### All replies

• ```select PART, '£'+convert(varchar(20),sum(deliveredqty*unitprice)/SUM(deliveredqty)) as AveragePerItem from #m
group by part```

PART           AveragePerItem
18-980012   £0.57

Best Regards,

If you have found this post helpful, please click the 'Vote as Helpful ' link (the green triangle and number on the top-left).

If this post answers your question, click the 'Mark As Answered ' link below. It helps others who experience the same issue in future to find the solution.

• Proposed as answer by Friday, September 18, 2009 7:50 AM
• Marked as answer by Tuesday, September 22, 2009 6:13 AM
Friday, September 18, 2009 6:57 AM
• Hi Melissa,

Thank you for your help. Though your suggestion does indeed give me a weighted average,I was hoping to work out the weighted average on a FIFO basis, i.e. First In First Out.

Thanks
Tuesday, September 22, 2009 3:05 PM
• It is.
FIFO is only applicable when you take something out from stock. Then you remove oldest item first and thus change current stock value.
Melissa is right for the stock value calculation.
Tuesday, September 22, 2009 6:12 PM