{"course":{"slug":"data-and-sql","title":"Data, SQL and Getting to the Answer","url":"https://addaly.com/learn/data-and-sql","level":"beginner","track":"build"},"lesson":{"slug":"reconciling-a-total","title":"Reconciling: making a total tie out before you send it","url":"https://addaly.com/learn/data-and-sql/reconciling-a-total","position":33,"of":66,"minutes":8,"answer":"Before a total leaves your hands, make the grouped parts sum to the ungrouped whole, keep a row-count ledger across each join, tie the figure to an external number with the difference explained.","keyIdea":"Before a total leaves your hands, make the grouped parts sum to the ungrouped whole, keep a row-count ledger across each join, tie the figure to an external number with the difference explained, and check one row by hand."},"body":{"format":"markdown","text":"## A total nobody has checked is a rumour\n\nEvery 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.\n\nThere are four comparisons, and a query that has passed all four is one you can put your name under.\n\n## One: the parts must sum to the whole\n\nRun the grouped query and the ungrouped one:\n\n> Figure: Four comparisons before a number leaves your hands. Steps: 1. Parts sum to the whole (grouped total against ungrouped total); 2. A row ledger per join (count after every step; a rise means a fan); 3. Tie to a known figure (and explain the difference, do not remove it); 4. One row by hand (add up a customer on paper). Each of these is a query, not a ritual, and each returns nothing when all is well. They catch the mechanical errors so that errors of meaning are the only ones left to argue about, which is a much better argument to be having.\n\n```sql\nSELECT sum(amount) FROM orders WHERE placed_at >= '2026-03-01' AND placed_at < '2026-04-01';\n-- 4,182,300\n\nSELECT city, sum(amount) FROM orders\nWHERE placed_at >= '2026-03-01' AND placed_at < '2026-04-01'\nGROUP BY city;\n-- 14 rows totalling 4,161,900\n```\n\nThe 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.\n\nA one-line version of the check, which should return zero:\n\n```sql\nSELECT (SELECT sum(amount) FROM orders WHERE ...) -\n       (SELECT sum(city_total) FROM (SELECT sum(amount) AS city_total FROM orders WHERE ... GROUP BY city) t);\n```\n\n## Two: the row ledger across joins\n\nWrite 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.\n\n## Three: tie to an external figure\n\nSomewhere 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.\n\nSet a tolerance in advance. For a monthly revenue figure a tenth of a percent is typical; anything larger needs a named cause.\n\n## Four: one row by hand\n\nPick 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.\n\n## Making it a query, not a ritual\n\nEach of these can be a query that returns rows only when something is wrong:\n\n```sql\n-- rows present in the total but absent from the breakdown\nSELECT count(*), sum(amount) FROM orders\nWHERE placed_at >= '2026-03-01' AND placed_at < '2026-04-01'\n  AND city IS NULL;\n```\n\nPut 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.\n\n## What reconciliation cannot do\n\nIt 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."},"check":{"question":"A grouped revenue-by-city report sums to ₹20,400 less than the ungrouped monthly total from the same table and date filter. What is the most likely explanation?","options":[{"text":"GROUP BY discards rows whose grouping column is NULL, so orders with no recorded city are dropped by the grouping itself before any sum runs.","correct":false,"tempting":"GROUP BY places NULL keys in their own pile and shows them as a row; they only vanish when a filter or an inner join removes them."},{"text":"`sum()` rounds each city's subtotal to the nearest hundred and the rounding losses accumulate.","correct":false,"tempting":"sum() does not round; rounding, if any, happens in a display step and could not lose ₹20,400 across fourteen rows."},{"text":"Orders with a NULL city were excluded from the grouped query by a filter or an inner join, and they carry the missing ₹20,400.","correct":true},{"text":"The grouped query ran a moment later and new orders were inserted in between, so the totals cover different data.","correct":false,"tempting":"New rows would make the grouped total larger than the earlier ungrouped one, not smaller."}]},"prev":{"slug":"pivoting-rows-to-columns","title":"Wide and long: pivoting rows to columns and back","url":"https://addaly.com/learn/data-and-sql/pivoting-rows-to-columns"},"next":{"slug":"what-a-window-is","title":"A window: GROUP BY that keeps the rows","url":"https://addaly.com/learn/data-and-sql/what-a-window-is"},"authorship":"Founder and series editor: Dr Mustafa Mun. Written by AI models to a written standard. Exam keys checked blind by a second model: 3,665 of 3,666 agreed. Academic review invited.","review":{"status":"Draft, open for academic review"},"cite":"Addaly (2026) 'Reconciling: making a total tie out before you send it', in Data, SQL and Getting to the Answer. Edited by M. Mun. Addaly. Available at: https://addaly.com/learn/data-and-sql/reconciling-a-total (Accessed: 13 October 2026).","licence":{"name":"CC BY-NC-SA 4.0","url":"https://creativecommons.org/licenses/by-nc-sa/4.0/"},"reader":{"schema":1,"title":"Reconciling: making a total tie out before you send it","courseTitle":"Data, SQL and Getting to the Answer","module":{"index":3,"slug":"summaries-and-the-piles-behind-them","title":"Summaries, and the piles behind them"},"answer":null,"keyIdea":"Before a total leaves your hands, make the grouped parts sum to the ungrouped whole, keep a row-count ledger across each join, tie the figure to an external number with the difference explained, and check one row by hand.","hero":{"kind":"figure","figure":{"t":"figure","label":"Figure","figure":{"type":"flow","steps":[{"label":"Parts sum to the whole","detail":"grouped total against ungrouped total"},{"label":"A row ledger per join","detail":"count after every step; a rise means a fan"},{"label":"Tie to a known figure","detail":"and explain the difference, do not remove it"},{"label":"One row by hand","detail":"add up a customer on paper"}],"title":"Four comparisons before a number leaves your hands","caption":"Each of these is a query, not a ritual, and each returns nothing when all is well. They catch the mechanical errors so that errors of meaning are the only ones left to argue about, which is a much better argument to be having."},"alt":"Figure: Four comparisons before a number leaves your hands. Steps: 1. Parts sum to the whole (grouped total against ungrouped total); 2. A row ledger per join (count after every step; a rise means a fan); 3. Tie to a known figure (and explain the difference, do not remove it); 4. One row by hand (add up a customer on paper). Each of these is a query, not a ritual, and each returns nothing when all is well. They catch the mechanical errors so that errors of meaning are the only ones left to argue about, which is a much better argument to be having."}},"blocks":[{"t":"h2","id":"a-total-nobody-has-checked-is-a-rumour","runs":[{"text":"A total nobody has checked is a rumour"}]},{"t":"p","runs":[{"text":"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."}]},{"t":"p","runs":[{"text":"There are four comparisons, and a query that has passed all four is one you can put your name under."}]},{"t":"h2","id":"one-the-parts-must-sum-to-the-whole","runs":[{"text":"One: the parts must sum to the whole"}]},{"t":"p","runs":[{"text":"Run the grouped query and the ungrouped one:"}]},{"t":"code","lang":"sql","label":"SQL","text":"SELECT sum(amount) FROM orders WHERE placed_at >= '2026-03-01' AND placed_at < '2026-04-01';\n-- 4,182,300\n\nSELECT city, sum(amount) FROM orders\nWHERE placed_at >= '2026-03-01' AND placed_at < '2026-04-01'\nGROUP BY city;\n-- 14 rows totalling 4,161,900"},{"t":"p","runs":[{"text":"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 "},{"text":"WHERE city IS NOT NULL","code":true},{"text":" 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."}]},{"t":"p","runs":[{"text":"A one-line version of the check, which should return zero:"}]},{"t":"code","lang":"sql","label":"SQL","text":"SELECT (SELECT sum(amount) FROM orders WHERE ...) -\n       (SELECT sum(city_total) FROM (SELECT sum(amount) AS city_total FROM orders WHERE ... GROUP BY city) t);"},{"t":"h2","id":"two-the-row-ledger-across-joins","runs":[{"text":"Two: the row ledger across joins"}]},{"t":"p","runs":[{"text":"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."}]},{"t":"h2","id":"three-tie-to-an-external-figure","runs":[{"text":"Three: tie to an external figure"}]},{"t":"p","runs":[{"text":"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."}]},{"t":"p","runs":[{"text":"Set a tolerance in advance. For a monthly revenue figure a tenth of a percent is typical; anything larger needs a named cause."}]},{"t":"h2","id":"four-one-row-by-hand","runs":[{"text":"Four: one row by hand"}]},{"t":"p","runs":[{"text":"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."}]},{"t":"h2","id":"making-it-a-query-not-a-ritual","runs":[{"text":"Making it a query, not a ritual"}]},{"t":"p","runs":[{"text":"Each of these can be a query that returns rows only when something is wrong:"}]},{"t":"code","lang":"sql","label":"SQL","text":"-- rows present in the total but absent from the breakdown\nSELECT count(*), sum(amount) FROM orders\nWHERE placed_at >= '2026-03-01' AND placed_at < '2026-04-01'\n  AND city IS NULL;"},{"t":"p","runs":[{"text":"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."}]},{"t":"h2","id":"what-reconciliation-cannot-do","runs":[{"text":"What reconciliation cannot do"}]},{"t":"p","runs":[{"text":"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."}]}],"check":{"question":[{"text":"A grouped revenue-by-city report sums to ₹20,400 less than the ungrouped monthly total from the same table and date filter. What is the most likely explanation?"}],"options":[{"text":[{"text":"GROUP BY discards rows whose grouping column is NULL, so orders with no recorded city are dropped by the grouping itself before any sum runs."}],"correct":false,"tempting":[{"text":"GROUP BY places NULL keys in their own pile and shows them as a row; they only vanish when a filter or an inner join removes them."}]},{"text":[{"text":"sum()","code":true},{"text":" rounds each city’s subtotal to the nearest hundred and the rounding losses accumulate."}],"correct":false,"tempting":[{"text":"sum() does not round; rounding, if any, happens in a display step and could not lose ₹20,400 across fourteen rows."}]},{"text":[{"text":"Orders with a NULL city were excluded from the grouped query by a filter or an inner join, and they carry the missing ₹20,400."}],"correct":true,"tempting":null},{"text":[{"text":"The grouped query ran a moment later and new orders were inserted in between, so the totals cover different data."}],"correct":false,"tempting":[{"text":"New rows would make the grouped total larger than the earlier ungrouped one, not smaller."}]}],"order":[3,1,2,0],"why":null},"prev":{"slug":"pivoting-rows-to-columns","title":"Wide and long: pivoting rows to columns and back","minutes":9},"next":{"slug":"what-a-window-is","title":"A window: GROUP BY that keeps the rows","minutes":9}}}