Reconcile Report Totals With SQL
A report shows one total and finance shows another. Use SQL to get the source figure, check orders against their line items, rebuild the report's total, and find the exact order that explains the gap, on a sample online shop database.
Step by step
Two numbers
- Which total is right?. The sales by region report shows one total and finance shows another. A few SQL queries can show which one is right and why.
- The source data. Start from the source: the orders table. Every report built on it should add up to the same figure.
Source total
- Source total. Step 1: add up the order_total column across every row in orders, and call the result total. It returns 15,322, which matches the finance figure.
SELECT SUM(order_total) AS total FROM orders;
Line items
- Check the lines. Step 2: check the detail behind it. Each order has lines in order_items, so add up quantity times unit_price across all the lines. It also returns 15,322. The orders and their lines agree, so the source figure is solid.
SELECT SUM(quantity * unit_price) AS items FROM order_items;
Rebuild the report
- Rebuild the report. Step 3: rebuild the report the way it is made. Join orders (o) to customers (c) on customer_id to get each order's region, then GROUP BY c.region. It returns 4 rows, one per region, just like the report. Next, turn them into one number. Replace c.region, SUM(o.order_total) AS sales with SUM(o.order_total) AS report_total, so the region column is gone. Then delete the GROUP BY c.region line and end the query with a semicolon, so it returns one total for all regions together. It returns 15,298, which is 24 less than the source. The join is losing something.
SELECT c.region, SUM(o.order_total) AS sales FROM orders AS o JOIN customers AS c ON c.customer_id = o.customer_id GROUP BY c.region;SUM(o.order_total) AS report_total;
Find the gap
- Find the gap. Step 4: list the orders whose customer_id is not in the customers table. NOT IN checks each value against the list the subquery returns. It returns 1 order worth exactly 24. Its customer doesn't exist, so the JOIN drops it from the report. That explains the gap.
SELECT order_id, customer_id, order_total FROM orders WHERE customer_id NOT IN ( SELECT customer_id FROM customers);
Recap
- Reconcile in four steps. To reconcile, get the source total, check it against the detail, rebuild the report's total, then find the rows that explain the difference.
Common mistakes
- Using NOT IN when the list can contain NULL. If the subquery returns even one NULL customer_id, NOT IN returns no rows at all, and orders with a NULL customer_id are never listed either. Use NOT EXISTS, or a LEFT JOIN with WHERE c.customer_id IS NULL.
- Duplicate keys in the joined table. If a customer_id appears twice in customers, the JOIN counts that customer's orders twice and the report total goes up, not down. Check with GROUP BY customer_id HAVING COUNT(*) > 1.
- Comparing the region rows by eye. Adding up 4 region figures by hand hides small gaps like 24. Remove the GROUP BY and let SQL return one report total to compare with the source.
- Expecting line items to always match order totals. They only match when order_total is just the sum of its lines. If it also includes shipping, tax or discounts, add those in before you compare.
When a business analyst uses this
During UAT of a new sales dashboard, finance rejects sign-off because the totals differ. The BA runs these 4 queries, finds the orders with no matching customer, and logs them as a data quality issue in the RAID log with the exact order IDs and value, so the data team can fix the source.
Questions people ask
Why does a JOIN make my SQL total smaller?
An inner JOIN only keeps rows that match on both sides. An order whose customer_id isn't in the customers table is dropped, so its value goes missing from the total.
How do I find rows in one table with no match in another?
Use NOT IN with a subquery, as in the step 4 query. If the other column can hold NULL, use NOT EXISTS or a LEFT JOIN with WHERE c.customer_id IS NULL instead.
Should the line items total match the orders total?
Only if order_total is just the sum of its lines. If it also includes shipping, tax or discounts, expect a difference and add those parts in before you compare.
