Calculate weighted average order value in SQL

Overall average order value is eligible order value divided by eligible order count. If you start from regional averages, weight each region by its number of eligible orders. A simple average of regional averages gives a small region the same influence as a large one.

The mistake is easy to make when a dashboard exports one row per region. The report looks neatly summarized, but the order counts that justify the averages may have been removed.

This walkthrough uses the synthetic commerce SQL lab. The completed-order population contains eight orders worth 104,000 paise, so its reference AOV is 13,000 paise, or ₹130. Refund-adjusted value is a separate metric and is not used here.

Calculate from the underlying population first

SELECT
    SUM(order_total_paise) AS value_paise,
    COUNT(*) AS orders,
    1.0 * SUM(order_total_paise) / NULLIF(COUNT(*), 0)
        AS aov_paise
FROM orders
WHERE status = 'completed';

The 1.0 requests a non-integer division result in SQLite. NULLIF makes a zero denominator produce NULL rather than suggesting that an undefined average is zero. The expected output is 104,000, eight and 13,000.

Eligibility must be identical in the numerator and denominator. Dividing completed-order value by all orders, including cancellations, would lower the result for a reason unrelated to basket size.

Missing monetary values also need a policy. SUM and AVG ignore NULL inputs, while COUNT star still counts rows. If an eligible order amount is unknown, disclose incomplete value coverage or stop the calculation; do not quietly mix a partial numerator with a complete denominator.

Build regional totals while preserving unknown customers

WITH regional AS (
    SELECT
        COALESCE(c.region, 'Unmatched') AS region,
        SUM(o.order_total_paise) AS value_paise,
        COUNT(*) AS orders
    FROM orders AS o
    LEFT JOIN customers AS c ON c.customer_id = o.customer_id
    WHERE o.status = 'completed'
    GROUP BY COALESCE(c.region, 'Unmatched')
)
SELECT region, value_paise, orders,
       1.0 * value_paise / orders AS regional_aov_paise
FROM regional
ORDER BY region;

The fixture produces:

All monetary columns are paise. The unmatched customer remains visible so the regional totals reconcile to the order-level population. In a production dataset, distinguish a missing customer from a known customer with missing region if both are possible.

Compare the wrong and right rollups

The unweighted average is (11000 + 18500 + 9000 + 12000) / 4 = 12625. It answers, “What is the average of these four regional averages when each region has equal weight?” That is not the same as the average order value across all orders.

The order-weighted result is:

(11000×2 + 18500×2 + 9000×1 + 12000×3) / 8 = 13000.

When sums and counts are available, use them directly rather than reconstructing totals from rounded averages:

WITH regional AS (
    SELECT COALESCE(c.region, 'Unmatched') AS region,
           SUM(o.order_total_paise) AS value_paise,
           COUNT(*) AS orders
    FROM orders AS o
    LEFT JOIN customers AS c ON c.customer_id = o.customer_id
    WHERE o.status = 'completed'
    GROUP BY COALESCE(c.region, 'Unmatched')
)
SELECT
    AVG(1.0 * value_paise / orders) AS unweighted_region_average,
    1.0 * SUM(value_paise) / SUM(orders) AS overall_aov_paise
FROM regional;

Expected: 12,625 versus 13,000. Keeping the additive components also avoids cumulative rounding error when a report is regrouped several times.

Choose weights from the question

Order counts are correct weights for an order-level average. They are not automatically correct for customer averages, item prices or survey results.

For average spend per customer, the denominator is eligible customers, and the underlying customer totals must be defined. For average unit selling price, quantities usually matter. For survey weighting, the weights may reflect a sampling design rather than observed group size.

Do not select weights merely because a convenient numeric column exists. State the unit whose average you intend to estimate, then derive the denominator from that unit.

Preserve the calculation's meaning in a dashboard

Store the value sum and eligible count alongside the ratio. Define the total-row measure as a ratio of totals rather than a sum or average of displayed ratios. If users can filter the dashboard, confirm that both components respond to the same eligibility rules.

Exercise: remove the unmatched category from the regional report. The remaining value is 95,000 paise across seven orders, giving approximately 13,571.43 paise. Explain why that is a valid average for a narrower population but not the original all-completed-order AOV.

The lab and SQLite aggregate reference help separate arithmetic from aggregation behaviour. For practice connecting SQL measures to dashboards, see NeuraPath's Data Analytics with Generative AI programme.

Continue learning

This article is part of the SQL foundations for reliable analysis sequence. Use the neighbouring tasks when you need the prerequisite or the next application.

• Review the prerequisite or neighbouring task in SQL dates: choose inclusive and exclusive boundaries correctly.
• Continue with SQL DISTINCT: when it fixes a count and when it hides a bug.