Weighted Averages in T-SQL

One expensive sale and a hundred inexpensive sales should not have equal influence on average selling price. Weighted averages give each observation the influence defined by its quantity or importance. The calculation is simple once both sums describe the same population.

Choose the Meaning of the Weight

An ordinary average treats each included row equally. A quantity-weighted selling price treats each sold unit equally. Both calculations can be correct, but they answer different business questions.

Start by identifying what one row represents. It could describe one transaction, one daily total, or one regional summary. A weight must express the intended contribution of that row to the final result.

I ask for the numerator and denominator in business words before writing the query. I also check whether the weight represents units, observations, or money. An unexplained weight column is a reliable way to produce a confident-looking wrong answer.

For a unit selling price, the numerator is total extended sales value. The denominator is the number of units contributing to that value. Dividing those sums gives price per unit across the selected population.

Compare Equal Row Weight with Quantity Weight

Run the sample in one connection so the temporary table remains available. It deliberately includes unequal quantities, a missing price, a missing quantity, and a zero quantity. Those cases make the population rules visible.

DROP TABLE IF EXISTS #WeightedSales;
CREATE TABLE #WeightedSales
(
    SaleId int NOT NULL PRIMARY KEY,
    SaleDate date NOT NULL,
    UnitPrice decimal(12,4) NULL,
    Quantity int NULL
);
INSERT #WeightedSales VALUES
(1, '20250101', 10.0000, 1),
(2, '20250101', 20.0000, 9),
(3, '20250102', 30.0000, 2),
(4, '20250104', 15.0000, 4),
(5, '20250105', NULL, 5),
(6, '20250105', 12.0000, NULL),
(7, '20250105', 40.0000, 0);
SELECT AVG(UnitPrice) AS EqualRowAverage,
       SUM(UnitPrice * CONVERT(decimal(12,0), Quantity))
       / NULLIF(SUM(CONVERT(decimal(12,0), Quantity)), 0) AS QuantityWeightedAverage
FROM #WeightedSales
WHERE UnitPrice IS NOT NULL AND Quantity > 0;

Both expressions use the same eligible rows for this comparison. The weighted result gives the larger quantity more influence. Calculate expected values from the declared inputs, then compare your returned results without assuming a particular display scale.

The formula is SUM(value multiplied by weight) divided by SUM(weight). Do not replace it with AVG(value multiplied by weight). That expression divides by a row count, which is a different denominator.

If you want all priced rows in the ordinary average, state that difference explicitly. Otherwise, the side-by-side comparison mixes populations as well as weighting methods. Readers then cannot tell which change caused the difference.

Handle Missing Values as Missing Information

A NULL weight means the contribution is unknown. A zero weight means its contribution is known to be zero. Converting every NULL to zero silently erases that distinction.

A missing value also needs a matching denominator decision. SUM ignores NULL expressions, so a missing price can disappear from the numerator automatically. Including its positive quantity in the denominator would then bias the result downward.

Filter both sums through one eligible population or apply the same condition to both expressions. Keep invalid records visible in a separate quality report. Excluding them from the calculation should not make them disappear from operational review.

SELECT SUM(CASE WHEN UnitPrice IS NULL THEN 1 ELSE 0 END) AS MissingPrices,
       SUM(CASE WHEN Quantity IS NULL THEN 1 ELSE 0 END) AS MissingWeights,
       SUM(CASE WHEN Quantity = 0 THEN 1 ELSE 0 END) AS ZeroWeights,
       SUM(CASE WHEN Quantity < 0 THEN 1 ELSE 0 END) AS NegativeWeights
FROM #WeightedSales;
SELECT SUM(UnitPrice * CONVERT(decimal(12,0), Quantity))
       / NULLIF(SUM(CONVERT(decimal(12,0), Quantity)), 0) AS WeightedPrice
FROM #WeightedSales
WHERE UnitPrice IS NOT NULL AND Quantity IS NOT NULL AND Quantity > 0;

NULLIF protects division when the eligible weight sum is zero. Return NULL for an undefined weighted price rather than announcing a price of zero. A report can label that result as unavailable while preserving its numeric meaning.

Negative quantities require a separate business rule. Returns can legitimately reduce net sales and units, but net weights can cancel to zero. Do not reuse the positive-sales calculation without deciding whether gross or net activity is the intended measure.

Preserve Enough Precision before Multiplication

Integer arithmetic can truncate division or overflow before an aggregate sees the expression. Casting only the finished result does not repair an earlier overflow. Convert operands to appropriate decimal types before multiplication and division.

The example uses decimal inputs and an explicit decimal quantity. That choice suits its declared small input domain. Production precision must accommodate your largest price, weight, product, and accumulated total.

SQL Server derives precision and scale for multiplication and division, with a maximum decimal precision of 38. Scale reduction and rounding remain possible. Inspect the required output precision instead of assuming every intermediate digit survives automatically.

Round at the agreed reporting boundary. Rounding each row's extended value first can differ from rounding the final aggregate. If accounting rules require row-level rounding, implement that intentionally and explain its effect on the numerator.

Build Moving Weighted Averages from Moving Sums

A moving weighted value requires a moving numerator and a moving denominator. Averaging previously calculated daily averages loses each day's original weight. Retain both components when summarizing the source.

The next query creates a continuous five-day calendar for this sample. Empty days contribute zero value and zero weight. That makes a three-row frame represent three calendar dates instead of three observed sales dates.

;WITH Daily AS
(
    SELECT SaleDate,
           SUM(UnitPrice * CONVERT(decimal(12,0), Quantity)) AS SalesValue,
           SUM(CONVERT(decimal(12,0), Quantity)) AS SoldUnits
    FROM #WeightedSales
    WHERE UnitPrice IS NOT NULL AND Quantity > 0
    GROUP BY SaleDate
), Calendar AS
(
    SELECT DATEADD(day, n, CONVERT(date, '20250101')) AS SaleDate
    FROM (VALUES (0),(1),(2),(3),(4)) AS d(n)
), CompleteDays AS
(
    SELECT c.SaleDate,
           COALESCE(d.SalesValue, CONVERT(decimal(38,4), 0)) AS SalesValue,
           COALESCE(d.SoldUnits, CONVERT(decimal(38,0), 0)) AS SoldUnits
    FROM Calendar AS c
    LEFT JOIN Daily AS d ON d.SaleDate = c.SaleDate
), Moving AS
(
    SELECT SaleDate,
           SUM(SalesValue) OVER
           (ORDER BY SaleDate ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS WindowValue,
           SUM(SoldUnits) OVER
           (ORDER BY SaleDate ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS WindowUnits
    FROM CompleteDays
)
SELECT SaleDate, WindowValue, WindowUnits,
       WindowValue / NULLIF(WindowUnits, 0) AS MovingWeightedPrice
FROM Moving
ORDER BY SaleDate;

The first dates have partial windows because earlier calendar dates are absent from this sample. Include the required earlier dates when the report needs full-width windows. Partition both sums by product or location when independent series share the same dataset.

A populated calendar matters whenever the requirement says days rather than observations. Without it, a missing date changes the window's meaning. Specify whether empty dates represent zero activity or unknown collection before filling them.

Reconcile Grouped and Combined Weighted Averages

Weighted averages remain consistent across groups when their underlying components are preserved. Add group numerators and denominators, then divide the combined sums. Do not give a tiny group the same influence as a large group by averaging their averages.

;WITH Groups AS
(
    SELECT SaleDate,
           SUM(UnitPrice * CONVERT(decimal(12,0), Quantity)) AS SalesValue,
           SUM(CONVERT(decimal(12,0), Quantity)) AS SoldUnits
    FROM #WeightedSales
    WHERE UnitPrice IS NOT NULL AND Quantity > 0
    GROUP BY SaleDate
)
SELECT AVG(SalesValue / NULLIF(SoldUnits, 0)) AS AverageOfDailyAverages,
       SUM(SalesValue) / NULLIF(SUM(SoldUnits), 0) AS CombinedWeightedPrice
FROM Groups;

I verify weighted averages by comparing the combined components with an ungrouped calculation. I also include a group with no eligible weight in the test. A denominator deserves at least as much attention as the attractive number above it.

What should a missing price do to its quantity in your report? Answer that before deciding how to handle NULL. Once that rule is explicit, the arithmetic becomes easier to test and much harder to misinterpret.

Preserve the eligible row count alongside the two sums when the report needs an audit trail. That count does not replace the weight denominator. It explains how many records contributed to the stated population and helps expose unexpected exclusions.

Related reading on this blog: T-SQL Window Function Framing and Performance: Notes from the Field #103 and SQL SERVER Challenge: Cumulative Total Calculation.

A weighted average is not an average of averages, it is one matched numerator divided by one matched denominator.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.