Reconcile totals and calculate a weighted average

A chart is easier to trust when its headline totals can be rebuilt from the accepted order rows. Use the same eligibility, date and dimension filters for count, sum and average.

For the default fixture, five completed orders contribute 5,750 net cents. Their average net value is 1,150 cents. The dataset also includes fractional-average cases so an accidental integer division cannot pass unnoticed.

COUNT(*) includes zero-value orders. SUM over no rows returns NULL, while COUNT returns zero. The report chooses zero for an empty money total and NULL for an average with no orders. Those choices mean different things.

Force real division in SQLite with a real-valued numerator such as 1.0*SUM(...). Use NULLIF on the denominator so an empty population remains explicitly undefined.

WITH input_orders(net_cents) AS (VALUES(100),(101),(0))
SELECT COUNT(*) AS completed_orders, SUM(net_cents) AS net_sales_cents,
       1.0*SUM(net_cents)/NULLIF(COUNT(*),0) AS avg_net_cents
FROM input_orders;

Do not average averages without weights. A day with one order and a day with nine orders should not receive equal weight in an overall per-order average. Retain the sum and count, combine them, then divide once.

Reconcile in both directions: the order rows sum to the KPI, and the per-day or per-channel groups sum to that same total. This works because every order belongs to one date and one channel. Overlapping categories or many-to-many membership would need another explicit allocation/counting policy.

Keep integer cents for totals. The average can be fractional cents as an analytical ratio; the display rounds currency separately. Neither display rounding nor chart scaling should change the underlying reconciled values.

WITH daily(net_cents,orders) AS (VALUES(100,1),(9000,9))
SELECT 1.0*SUM(net_cents)/SUM(orders) AS weighted,
       AVG(1.0*net_cents/orders) AS unweighted
FROM daily;

Write the one-row aggregate. Test an empty population, actual zero-value orders, negative adjustments and a fractional result. Reconcile it with the previous lesson’s eligible order rows.

Next we build that trustworthy order grain from raw one-to-many line and refund relationships.