SQLTeam.com | Weblogs | Forums

Weighted average purchase price of a stock

Dear All,

I need to generate weighted average purchase price of a stock as shown. Kindly help me out with this problem.

It is difficult, if not impossible, for someone to discern the rules for computing the results that you are looking for just by looking at the screenshot. For a simple weighted average price, the formula would be SUM(Quantity*Price)/SUM(Quantity). However, based on your screenshot, it doesn't look like that is what you are after.

You have to at least explain your data and logic.

What is FRESH & COVER ? What is your formula for weighted average price ?

Hi Khtan,

Fresh is the buy and cover is sell. we have to generate weighted average purchase price against the cover flag. Our main goal is to calculate profit or loss made by customer when he is selling based on the purchase price.
for example,


the calculation is the normal practice in your organization ?

It seems unconventional to me.

What version of SQL Server are you on? 2012 or is it an earlier version? If you are on SQL 2012 or later, look up the windowing function SUM() OVER() which lets you calculate running totals. Running totals is what you will need to compute the average prices. If you are on an earlier version the query will be harder to write.