1

I found this old question which brings a nice approach for calculating weighted average prices. It basically consists in grouping by the stock name and then fetching the sum(quantity*price)/sum(quantity)

But in day trades you buy and sell the asset in the same day, meaning that the final quantity of the day is zero and sql returns: Divide by zero error encountered

A examples would be

3 trades for the same stock

 1. Price 10 Quantity 
 2. 100 Price 8 Quantity 100
 3. Price 30 Quantity 200

Do you guys know some workaround ? Is there a way to group trades with positive and negative quantities separately ?

Community
  • 1
  • 1
Pedro Braz
  • 2,261
  • 3
  • 25
  • 48
  • Most likely. But with no more details than you posted that is about as close as we can get. – Sean Lange Apr 24 '15 at 15:02
  • use a `case` statement inside 2 seperate `sum()` statements `SUM(case when qty >0 then qty*price_per else 0 END)` type idea – Daniel E. Apr 24 '15 at 15:02

2 Answers2

2

sure, add a grouping bucket defined by the sign of the amount...

Select assetIdentifier, 
    case when amount > 0 then 'debit' else 'credit' end typeTx, 
     Avg(Amount) 
from table
group by assetIdentifier,
   case when amount > 0 then 'debit' else 'credit' end

or, if you want both values on a single output row,

Select assetIdentifier, 
    avg(case when amount > 0 then amount end) debit , 
    avg(case when amount < 0 then amount end) credit
from table
group by assetIdentifier
Charles Bretana
  • 143,358
  • 22
  • 150
  • 216
1

The formula for the weighted average is:

sum(quantity*price)/sum(quantity)
------------------------^ NOT price

If you want to ignore the direction of the trade, then just use absolute value:

sum(abs(quantity)*price)/sum(abs(quantity))
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786