2

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?

daShier
  • 2,056
  • 2
  • 8
  • 14
Cooper
  • 131
  • 1
  • 9
  • 1
    Sample data is best served as tabular text. Also, would you please show us your expected output, as it hard to understand what you are trying to achieve here. – GMB Sep 17 '19 at 23:28
  • I don't think you have your formula right: `price*units)/units` is simply `price`. For a weighted average you need to have multiple prices. So unless each item had multiple prices, then you will only be able to calculate a single weighted average for all items. Is that what you are after? Is the `Price` column the price of a single unit (as I would assume), or the total price for all units? – daShier Sep 18 '19 at 00:01
  • 1
    @vineet . . . Please show the results you want from the query and how the values should be rolled up. – Gordon Linoff Sep 18 '19 at 00:46
  • 1
    I do not know the answer :(. I am just trying to figure out what the correct way of finding a weighted average is, even when there are multiple categories and sub-categories. – Cooper Sep 18 '19 at 14:07

1 Answers1

1

Is this what you want?

select l1,
       sum(units * price) / nullif(sum(units), 0) as weighted_price
from t
group by l1;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786