By Prakhar Shrivastava · October 15, 2026

Average order value is eligible revenue divided by eligible orders. If your table contains one row per order line, AVG(line_usd) averages lines, not orders. First sum the lines belonging to each order, then average those order totals under the same eligibility and revenue definition.

A small US and UK export

Consider fictional completed orders for the same reporting month. All amounts are in USD under an agreed reporting-currency policy; the query does not convert currencies. The US has order 101 with two lines worth $30 and $20, and order 102 with one $100 line. The UK has order 201 with $10, $20, and $30 lines, plus order 202 with one $40 line.

The US therefore has $150 across two orders, so AOV is $75. Its three lines average $50. The UK has $100 across two orders, so AOV is $50. Its four lines average $25. Both line averages are valid descriptions of line amounts, but neither answers the order-level question.

Run the complete SQL example

WITH lines(market,order_id,line_id,line_usd) AS (
 VALUES ('US',101,1,30.0),('US',101,2,20.0),
        ('US',102,1,100.0),
        ('UK',201,1,10.0),('UK',201,2,20.0),('UK',201,3,30.0),
        ('UK',202,1,40.0)
), orders AS (
 SELECT market,order_id,SUM(line_usd) AS order_usd
 FROM lines GROUP BY market,order_id
)
SELECT market,COUNT(*) AS orders_count,
 SUM(order_usd) AS revenue_usd,AVG(order_usd) AS aov_usd
FROM orders GROUP BY market ORDER BY market;

Expected rows are UK: 2 orders, $100 revenue, $50 AOV; US: 2 orders, $150 revenue, $75 AOV. The combined population has four orders, $250 revenue, and $62.50 AOV. Three result checks covering the market summaries, incorrect line averages, and combined order result passed in SQLite 3.50.4.

Why the order key matters

The example groups by market and order ID because order IDs may be scoped to a market. In your actual source, identify the documented unique order key. If IDs are only unique within a store, add the store identifier. If an order can appear in multiple market exports, establish ownership or deduplication before grouping; adding market to the key must not turn one real order into two.

Each source line must also appear once. Summing to order grain does not repair duplicated line records. Reconcile a unique order/line key before calculating revenue, and investigate why duplicate rows exist. The duplicate-join example explains why distinct monetary amounts are not a safe deduplication rule.

Can you divide the line total by distinct orders?

Yes, when the input is complete, each eligible line appears once, order identifiers are correct, and the numerator and denominator describe the same population. Summing these seven line amounts and dividing by the four distinct orders also gives $62.50.

The two-stage approach makes the intermediate order totals inspectable and supports order-level checks. Avoid joining an order-header total onto every line and then summing that repeated total: orders with more lines would contribute their full value more than once. Also avoid averaging an order total repeated across lines, which weights each order by its number of lines.

Define revenue and eligibility before comparing markets

Specify whether the metric includes discounts, tax, shipping, refunds, or cancellations. The fixture assumes the supplied line amounts are the complete eligible revenue and that all four orders qualify. A real report needs a documented policy and a consistent cutoff. Filtering individual lines can change an order total without removing the order; decide whether the question concerns whole orders or only a product category.

For a US and UK comparison, do not add USD and GBP amounts as if they were the same unit. Use the agreed conversion method or publish separate local-currency metrics. Dates must also use the agreed reporting timezone and a complete period. These are reporting choices, not facts that AVG can infer.

Handle missing amounts and empty populations explicitly

SQL aggregates can ignore NULL amounts. An order with one known line and one unknown line may therefore receive a plausible but incomplete sum. Audit unknown amounts before relying on the order totals. Do not replace missing revenue with zero unless the source meaning justifies it. A confirmed zero-value eligible order is different from an order whose amount has not arrived.

If there are no eligible orders, AOV is unavailable, not zero. When computing the ratio explicitly, guard a zero denominator and retain an order-count column. If you average market AOVs to form an overall report, weight by eligible order counts or recompute from combined revenue and orders. The two markets here happen to have equal order counts; that coincidence should not become a general rule.

Primary reference: PostgreSQL aggregate functions. Continue with NULL versus zero, the weighted-rate exercise, and the revenue investigation case. This guide isolates order-versus-line grain; it does not claim a commercial benchmark or a cause of revenue changes.