Calculating Weighted Averages in SQL

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.

Weighted Averages by Group

The formula becomes more useful when you calculate it per category, campaign, or day instead of once for the whole table.

For example, if you want a weighted average rating per product category:

SELECT
    category,
    SUM(rating * review_count) * 1.0 / NULLIF(SUM(review_count), 0) AS weighted_avg_rating
FROM product_rating_summary
GROUP BY category;

This pattern shows up constantly in analytics work:

• average selling price weighted by units sold
• average score weighted by response count
• blended interest rate weighted by loan balance
• average price weighted by purchased quantity

The SQL itself is simple. The hard part is choosing the correct weight that matches the business question.

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;

You should also decide how to treat NULL values. In many cases, the safest pattern is to exclude rows where either the value or the weight is missing:

SELECT
    SUM(value * weight) * 1.0 / NULLIF(SUM(weight), 0) AS weighted_avg
FROM table_name
WHERE value IS NOT NULL
  AND weight IS NOT NULL;

The Most Common Mistake: Wrong Grain After a Join

Weighted averages often go wrong after a join. Suppose you join orders to order items and then compute a weighted metric at the wrong grain. If one row is duplicated three times by the join, its contribution is tripled.

That is why you should ask two questions before trusting the result:

1. What does one row represent before the join?
2. What does one row represent after the join?

If the grain changed, you may need to aggregate first and only then calculate the weighted average.

For example:

WITH order_level_metrics AS (
    SELECT
        order_id,
        SUM(line_revenue) AS order_revenue,
        SUM(quantity) AS order_units
    FROM order_items
    GROUP BY order_id
)
SELECT
    SUM(order_revenue) * 1.0 / NULLIF(SUM(order_units), 0) AS avg_price_per_unit
FROM order_level_metrics;

This structure keeps the metric aligned with the intended level of analysis.

When a Weighted Average Is Not Enough

Weighted averages are useful, but they can still hide important variation. Two campaigns can have the same weighted conversion rate while having very different distributions by region, device, or customer segment.

Use a weighted average when you need a single summary number. But consider adding:

• percentiles when skew matters
• segment-level breakouts when different groups behave differently
• counts alongside the metric so readers can judge reliability

That is often what turns a "technically correct" KPI into a decision-ready metric.

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)!

Related Articles

• SQL Aggregate Functions: COUNT, SUM, AVG, MIN, MAX Explained for the aggregate building blocks behind weighted calculations.
• Descriptive Statistics in SQL: Beyond Average and Count for the broader statistics toolkit when a single average is not enough.
• Calculating Percentiles and Median in SQL for the distribution-aware alternatives to average-heavy analysis.
• Analyzing A/B Test Results with SQL for another case where the metric formula is simple but data grain and denominator choice determine whether the result is trustworthy.