BA Prince the B.A. Subscribe

Home / SQL for Business Analysts

Reconcile Report Totals With SQL

Reconcile Report Totals With SQL

Watch on YouTube Subscribe

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 says one total. Finance says another. Which one is right? A business analyst can settle it with a few SQL queries. Let's reconcile the numbers.
The source data. The source is the orders table. Every report should agree with it.

Source total

Source total. Step one. Get the source figure. Sum of the order total, across every order. Fifteen thousand three hundred and twenty two. That matches finance.
```
SELECT SUM(order_total) AS total
FROM orders;
```

Line items

Check the lines. Step two. Check the detail behind it. Each order has lines in order items. Quantity times unit price, summed, should give the same figure. Fifteen thousand three hundred and twenty two again. Orders and their lines agree, so the source is solid.
```
SELECT SUM(quantity * unit_price) AS items
FROM order_items;
```

Rebuild the report

Rebuild the report. Step three. Rebuild the report the way it's made. It joins orders to customers to get the region, then groups by region. Four regions, just like the report. Now let's add them up into one number. Drop the region column, and call the sum report total. And remove the group by, so we get one total for everything. Fifteen thousand two hundred and ninety eight. Twenty four 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 four. Find orders whose customer ID is not in the customers table. Not in checks a value against a list. One order, worth exactly twenty four. Its customer doesn't exist, so the join drops it from the report. Gap explained.
```
SELECT order_id, customer_id, order_total
FROM orders
WHERE customer_id NOT IN (
  SELECT customer_id FROM customers);
```

Recap

Reconcile in four steps. So, get the source total, check it against the detail, rebuild the report's total, then hunt for the rows that explain the difference.

More in SQL for Business Analysts

SQL for Business Analysts: The 5 Queries You Actually Need

SQL SELECT Basics: Get Data Out of a Table (Beginner Tutorial)

SQL WHERE Clause: Filter Rows Like an Analyst

SQL INNER JOIN Explained With a Simple Example