Query to find and average weighted price for day trades

Question

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 ?

Answer 1

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