Weighted Aggregates: Weighted Average and Weighted Sum in SQL

1. Why a plain average is sometimes the wrong metric

Weighted aggregates solve a problem that a naive AVG regularly overlooks: not every value in a group should contribute equally to the result. A typical example is the average selling price of a product across multiple orders. A plain AVG(price) treats every order line equally, regardless of whether it sold one unit or a hundred units, which substantially skews the actual average selling price in favor of small orders.

Weighted aggregates solve this problem by multiplying every value by an associated weight before summing, then dividing by the sum of the weights instead of the plain row count. For price calculations, the weight would be the quantity sold, for a grade calculation the exam share, for a portfolio return the capital invested per position. The principle stays identical in every case, only the business weight changes depending on the use case.

This article shows the general formula for weighted aggregates, works through three concrete use cases, average price, grade calculation, and portfolio return, and covers common pitfalls such as division by zero and correctly handling negative or missing weights.

2. The formula: SUM(value times weight) divided by SUM(weight)

The general formula for weighted aggregates is SUM(value times weight) divided by SUM(weight), where value is the quantity being averaged and weight indicates how strongly each individual row should contribute to the overall result. This formula is a direct generalization of the simple arithmetic mean: set every weight to the constant value one, and SUM(value times one) divided by SUM(one) yields exactly the same value as AVG(value), because SUM(one) across n rows simply equals n.

The decisive difference shows up once the weights actually differ. A row with a high weight contributes proportionally more to both the numerator AND the denominator than a row with a low weight, so the final result automatically reflects the actual business significance of every individual row. This formula for weighted aggregates can be implemented directly in any SQL database without an extension, using plain SUM and a simple division, no special WEIGHTED_AVG function is required.

-- General formula for weighted aggregates
SELECT
    SUM(value * weight) / NULLIF(SUM(weight), 0) AS weighted_average
FROM measurements;

-- Compare with a plain, unweighted average
SELECT AVG(value) AS simple_average FROM measurements;
-- The two results diverge as soon as weights actually differ per row

3. Weighted average price across multiple orders

Probably the most common practical use case for weighted aggregates in e-commerce is a product's average selling price across multiple orders, weighted by the quantity sold. A plain AVG(unit_price) would weigh an order of a single unit exactly as much as a bulk order of a hundred units, even though the latter influences the actual average selling price far more strongly.

With weighted aggregates, the quantity sold is used as the weight: SUM(unit_price times quantity) equals the product's total revenue, SUM(quantity) the total number of units sold. The quotient of both sums yields the true, quantity-weighted average price, which corresponds exactly to total revenue divided by total quantity, instead of an average across individual order lines that carry completely different real-world weight depending on order size.

-- Quantity-weighted average selling price per product
SELECT
    product_id,
    SUM(unit_price * quantity) / NULLIF(SUM(quantity), 0) AS weighted_avg_price,
    AVG(unit_price)                                       AS naive_avg_price
FROM order_items
GROUP BY product_id;

-- weighted_avg_price reflects the true revenue-per-unit,
-- naive_avg_price is skewed toward small orders

4. Weighted grade calculation with different exam shares

A classic example of weighted aggregates outside e-commerce is grade calculation in education systems, where different exam formats are meant to contribute unequally to the final grade. A final exam typically counts more than a weekly quiz, and a plain AVG(score) would ignore this deliberate business imbalance and treat every exam equally, regardless of its actual importance to the overall assessment.

With weighted aggregates, every exam is assigned a numeric weight factor, for example one for a quiz and three for a final exam. SUM(score times weight_factor) divided by SUM(weight_factor) then yields the correctly weighted final grade, in which a final exam counts exactly three times as much as a single quiz, without the calculation needing to duplicate the same grade row multiple times in the table.

-- Weighted grade average: final exams count 3x, quizzes count 1x
SELECT
    student_id,
    SUM(score * weight_factor) / NULLIF(SUM(weight_factor), 0) AS weighted_final_grade
FROM (
    SELECT student_id, score, 1 AS weight_factor FROM quiz_scores
    UNION ALL
    SELECT student_id, score, 3 AS weight_factor FROM exam_scores
) AS all_scores
GROUP BY student_id;

5. Weighted portfolio return across multiple positions

In finance, weighted aggregates are the standard technique for computing the overall return of a portfolio made up of several individual positions. A position with a high investment amount should influence the overall return more strongly than a position with a small amount of invested capital, which is why a plain AVG(return) across all positions would be fundamentally wrong here and would distort an investor's actual economic situation.

The correct calculation weights every individual return by the invested capital of that particular position: SUM(return times invested_capital) divided by SUM(invested_capital) yields the capital-weighted overall return of the portfolio. This metric corresponds exactly to total profit divided by total capital, which is economically the only sensible interpretation of a portfolio return across multiple differently sized positions.

-- Capital-weighted portfolio return across multiple positions
SELECT
    portfolio_id,
    SUM(return_pct * invested_capital) / NULLIF(SUM(invested_capital), 0) AS weighted_portfolio_return,
    AVG(return_pct) AS naive_avg_return
FROM portfolio_positions
GROUP BY portfolio_id;

-- weighted_portfolio_return reflects actual profit / total capital,
-- naive_avg_return treats a small and a large position as equally important

6. Weighted aggregates combined with GROUP BY

Like any other aggregate function, weighted aggregates combine easily with GROUP BY to compute the weighted metric not just for the entire table, but for each group individually. A report that needs to show the quantity-weighted average price not just globally, but per product category or per calendar week, simply extends the formula with an appropriate GROUP BY clause, without changing anything about the actual SUM formula for weighted aggregates.

It is important that both sums, numerator and denominator, are computed within the same group, which is automatically the case with GROUP BY, because both SUM calls operate at the same grouping level. A common mistake instead arises when developers try to combine weighted aggregates across multiple separate queries with different grouping, which easily leads to inconsistent numerator-denominator pairs when the two subqueries are accidentally filtered differently.

7. Negative weights and handling cancellations

A peculiarity of weighted aggregates in an order context concerns cancellations and returns, which are often represented as negative quantities in the same table. A cancelled order line with a negative quantity affects both the numerator SUM(price times quantity) and the denominator SUM(quantity), which is correct from a business perspective in most cases, because a cancellation should reduce both revenue and the actually sold quantity accordingly.

It becomes problematic when the sum of weights turns negative or near zero due to predominant cancellations, because the division then returns either a sign-flipped or an extremely unstable result. In such cases, you should check before dividing whether the denominator actually yields a meaningful, positive value, and if necessary build in an explicit special case for groups with predominantly cancelled quantities, instead of blindly relying on the standard formula for weighted aggregates.

-- Guarding weighted aggregates against a near-zero or negative weight sum
SELECT
    product_id,
    SUM(quantity)                                          AS net_quantity,
    CASE
        WHEN SUM(quantity) <= 0 THEN NULL  -- flag instead of a misleading number
        ELSE SUM(unit_price * quantity) / SUM(quantity)
    END AS weighted_avg_price_safe
FROM order_items  -- cancellations stored as negative quantity
GROUP BY product_id;

8. Pitfalls: division by zero and NULL weights

The most common technical pitfall with weighted aggregates is division by zero, which occurs as soon as a group consists exclusively of rows with a weight of zero, or contains no rows at all. NULLIF(SUM(weight), 0) is the standard solution, turning the denominator into NULL as soon as it equals zero, so the division reliably returns NULL instead of a runtime error, entirely analogous to the general NULLIF pattern for any division in SQL.

A second, subtler pitfall concerns NULL values in the weight itself. If the weight column contains NULL instead of a number, the affected row is effectively ignored in both numerator and denominator, because both the multiplication value times weight and the weight itself become NULL and get skipped by SUM. In many cases that is the desired behavior, but it should be checked deliberately, because a missing weight sometimes should mean "weight zero" from a business perspective rather than "unknown weight", which is correctly modeled with COALESCE(weight, 0) instead of relying on implicit NULL behavior.

9. Plain average vs. weighted average compared

The following overview compares plain and weighted average across their key properties.

Anyone reporting a metric where individual rows should carry different weight, such as quantities, investment amounts, or weighting factors, should generally use weighted aggregates instead of a plain AVG, even if the difference initially seems small with the data at hand.

Weighted aggregates are not an exotic piece of specialist knowledge, but a direct extension of SUM available in every SQL database, indispensable wherever individual rows should carry a different amount of business weight.

10. Summary

Weighted aggregates compute averages and sums where every row counts to a different degree according to a business weight such as quantity, exam share, or invested capital. The formula SUM(value times weight) divided by SUM(weight) generalizes the simple arithmetic mean and yields exactly the same result as AVG when weights are equal.

Typical use cases are quantity-weighted average prices, weighted grade calculations, and capital-weighted portfolio returns. NULLIF reliably protects against division by zero when the sum of weights in a group is zero, and negative weights from cancellations require special attention when they make the overall weight sum negative.

11. FAQ: Weighted Aggregates