Unit 10.00: Row-count reconciliation as a habit

Unit ID: SQL-M10-U01 - Estimated active time: 14-17 minutes Objective: verify that a result covers the population you intended, using counts rather than belief.

Reconciliation is arithmetic, not judgement

Every module so far produced a number. This one is about proving those numbers before anyone else sees them - and the cheapest proof is that the parts sum to the whole.

SELECT
  (SELECT COUNT(*) FROM orders)                                AS all_orders,      -- 1000
  (SELECT COUNT(*) FROM orders WHERE status = 'completed')     AS completed,       -- 988
  (SELECT COUNT(*) FROM orders WHERE status = 'pending')       AS pending;         -- 12

988 + 12 = 1,000. If it did not, a third status exists that you have not accounted for - and your two-bucket report is quietly incomplete.

The same discipline on money

SELECT
  (SELECT SUM(order_total) FROM orders)                            AS all_revenue,       -- 2701463.00
  (SELECT SUM(order_total) FROM orders WHERE status='completed')   AS completed_revenue, -- 2685905.00
  (SELECT SUM(order_total) FROM orders WHERE status='pending')     AS pending_revenue;   -- 15558.00

2,685,905 + 15,558 = 2,701,463. Exact. A single query that makes the relationship between your headline figure and the total undeniable.

Reconciling against an independent source

The strongest check compares two paths to the same truth. Revenue from orders should equal money recorded in payments:

SELECT
  (SELECT SUM(order_total) FROM orders WHERE status = 'completed') AS order_revenue,
  (SELECT SUM(amount)      FROM payments)                          AS payments_total,
  (SELECT SUM(amount) FROM payments)
    - (SELECT SUM(order_total) FROM orders WHERE status='completed') AS difference;
-- 2685905.00 | 2685905.00 | 0.00

Zero difference. Two tables, populated by different logic, agreeing exactly. That is far stronger evidence than running the same query twice.

Note what this check survives: the instalment payments from Module 5 mean payments has 1,185 rows against 988 orders - different grains, same total. Reconciling on the measure rather than the row count is what makes it work.

Practice

Reconcile order items against orders: confirm every order has items and that the item count matches the expected total.

Check your answer

SELECT
  (SELECT COUNT(*) FROM order_items)                     AS item_rows,        -- 3400
  (SELECT COUNT(DISTINCT order_id) FROM order_items)     AS orders_with_items,-- 1000
  (SELECT COUNT(*) FROM orders)                          AS all_orders;       -- 1000

1,000 orders and 1,000 orders-with-items means none are missing. 3,400 item rows across 1,000 orders is the fan-out ratio from Module 5 - expected, and now confirmed rather than assumed.

Takeaway

Make the parts sum to the whole, and reconcile against an independent table whenever one exists. A zero difference is the cheapest strong evidence you can produce.

---