Reconciling: making a total tie out before you send it

A total nobody has checked is a rumour

Every number in this module can be produced by a query that runs without error and returns something plausible. The only way to know it is right is to compare it with something else that ought to agree with it. Accountants call this reconciliation, and they have done it for five centuries because it works.

There are four comparisons, and a query that has passed all four is one you can put your name under.

One: the parts must sum to the whole

Run the grouped query and the ungrouped one:

SELECT sum(amount) FROM orders WHERE placed_at >= '2026-03-01' AND placed_at < '2026-04-01';
-- 4,182,300

SELECT city, sum(amount) FROM orders
WHERE placed_at >= '2026-03-01' AND placed_at < '2026-04-01'
GROUP BY city;
-- 14 rows totalling 4,161,900

The grouped rows add up to 20,400 less than the whole. That gap is orders with a NULL city. GROUP BY puts them in their own pile, so they should appear as a row with a NULL key, unless a filter such as WHERE city IS NOT NULL or an inner join to a cities table removed them. Either way the report now silently excludes ₹20,400, and the reader will assume the fourteen rows are everything.

A one-line version of the check, which should return zero:

SELECT (SELECT sum(amount) FROM orders WHERE ...) -
       (SELECT sum(city_total) FROM (SELECT sum(amount) AS city_total FROM orders WHERE ... GROUP BY city) t);

Two: the row ledger across joins

Write down the count at each step: 31,940 orders in the table; 31,940 after the LEFT JOIN to customers; 31,940 after the LEFT JOIN to the pre-aggregated items summary. The moment a count rises, a join has fanned; the moment it falls, an inner join or a WHERE on the right-hand side has dropped rows. The lesson on joins made this the check for a single join. Across a real query with four joins, the ledger is the only way to see which one did it.

Three: tie to an external figure

Somewhere there is a number produced by a different system: the finance ledger, the payment provider’s dashboard, last month’s report that went to the board. Compare. The figures will not match exactly, and the useful work is explaining the difference, not making it go away. Refunds recorded on a different date. A currency conversion applied at a different rate. Timing: a payment that settled on 1 April for an order placed on 31 March. A reconciliation that ends with “the ₹38,200 difference is 41 refunds processed after month end” is a result. One that ends with “close enough” is not.

Set a tolerance in advance. For a monthly revenue figure a tenth of a percent is typical; anything larger needs a named cause.

Four: one row by hand

Pick a customer. Look up their orders in the source system. Add them up on paper. Compare with the report’s row. This catches the class of error that no total can: a join that pairs the right sum with the wrong name, a time zone that moved one order into the wrong month, a status filter that excluded a legitimate order. It takes three minutes and it is the check most often skipped.

Making it a query, not a ritual

Each of these can be a query that returns rows only when something is wrong:

-- rows present in the total but absent from the breakdown
SELECT count(*), sum(amount) FROM orders
WHERE placed_at >= '2026-03-01' AND placed_at < '2026-04-01'
  AND city IS NULL;

Put these in a file next to the report query. Run them together. A report that arrives with its reconciliation queries and their zero-row results has done something a bare number cannot: it has shown its work.

What reconciliation cannot do

It cannot tell you the definition was right. If “revenue” should have excluded cancelled orders and the query included them, every check above passes and the number is still wrong, because the external figure was computed the same wrong way or was never computed at all. That failure belongs to the question, and the final module of this course is about it. Reconciliation catches the mechanical errors so that the meaning errors are the only ones left to argue about.