CODEWITHSUNDEEP
sqlhiveweighted-average
Cooper
Cooper

Reputation: 131

Howe to calculate the weighted average price having multiple categories and subcategories?

enter image description here

I want to calculated the weighted average price in SQL for each level i.e. L0,L1, and L2. Currently, I only know the formula for calculating the general weighted avg = sum(price*units)/sum(units). 

    SELECT COALESCE((price*units)/units, 0) AS price_weighted

How should I roll it up to any category I want?

Upvotes: 2

Views: 898

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269643

Is this what you want?

select l1,
       sum(units * price) / nullif(sum(units), 0) as weighted_price
from t
group by l1;

Upvotes: 1

Related Questions