The standard AVG() function in SQL is great, but it treats every row equally. In the real world, some data points matter more than others.
If you buy 1 share of stock at $100 and 100 shares at $200, your average purchase price isn't $150. It's much closer to $200. This is where Weighted Averages come in.
The Formula
The formula for a weighted average is:
Weighted Average = Sum(Value * Weight) / Sum(Weight)
In SQL, this translates to:
SUM(value_column * weight_column) / SUM(weight_column)
Real-World Example: Product Reviews
Imagine you want to compare the quality of two products based on user ratings.
- Product A: 100 reviews, average rating 4.5
- Product B: 5 reviews, average rating 5.0
A simple average of the "average ratings" would be misleading if we were aggregating categories. But let's look at a portfolio example where this is clearer.
Investment Portfolio
We have a table of investments with share_price and quantity.
| stock | share_price | quantity |
|---|---|---|
| APPL | 150.00 | 10 |
| GOOG | 2800.00 | 1 |
| MSFT | 300.00 | 5 |
If we run AVG(share_price), we get: (150 + 2800 + 300) / 3 = 1083.33.
But that's meaningless because we only own 1 share of the expensive Google stock!
Let's calculate the true weighted average price of our portfolio.
Handling Division by Zero
One edge case to watch out for is when the sum of your weights is 0. This will cause a division by zero error.
You can handle this with NULLIF:
SELECT
SUM(value * weight) / NULLIF(SUM(weight), 0)
FROM table_name;
Summary
When analyzing data where volume, frequency, or importance varies, don't settle for AVG(). The Weighted Average gives you the true center of mass for your data.
Remember the pattern: SUM(val * weight) / SUM(weight)!