The SQL ran. Did it answer the right question?

Define the number before inspecting the query

We want the total for order-101 and order-102, counted once each. Both are USD 100, so the answer is USD 200. These teaching records have one currency and no tax, discounts, cancellations, or conversion. Keep that definition beside the calculation so another person can check what belongs in the total.

Don't label the result revenue. An order can exist before revenue is recognized or cash arrives. If the manager wants recognized revenue, agree on the source and recognition rules before reusing an order-total calculation.

Ask what one row represents. Analysts call this the grain. In the orders table, one row is one order. In the line-items table, one row is one item in an order. Joining them creates a row for each matching item, with the order total copied onto every row. The amount column keeps its name, but adding it now counts an order more than once.

Count what the join repeats

Add the order-total column and you get 100 + 100 + 100 + 100 + 100 = USD 500. There are still only two orders. Count unique order IDs as well as rows, then compare them with the orders the manager asked about. A query returning five rows may be working as written while calculating the wrong thing.

Joining another table can repeat the amounts again. An order with several status events could appear once for each item and each matching event. Before adding that table, write down how many records should match each order. Check the actual matches and missing records rather than trusting a plausible grand total.

Keep different orders with equal amounts

DISTINCT can keep unique amounts rather than unique orders. That looks tempting because all the repeated totals are USD 100. In this example it returns one USD 100 value and discards the other real order. The two orders share an amount, not an identity. Replacing USD 500 with USD 100 trades one incorrect answer for another.

Calculate from one valid total per order ID. You can use the original order rows or first establish one consistent total for each unique order in the joined data. Check disagreements instead of taking the first row. If one order appears as both USD 100 and USD 120, find which source or version is right before adding it.

Adding the five line amounts also gives USD 200 here, because this example defines each order total as the sum of its complete lines. Don't assume that in a real system. Missing items, order-level discounts, tax, returns, or different status filters can make the line sum differ from the order total. Check those rules before using it as a repair.

Check the orders behind the total

The worksheet keeps all three answers: USD 500 from repeated order totals, USD 100 from distinct amounts, and USD 200 from the original order rows. We check this arithmetic in JavaScript; no SQL database, generated query, or model was tested. For a real query, compare its answer with an agreed reference using the same source version, order IDs, filters, currency, time period, and calculation rule.

Compare order IDs as well as money. Leave out one USD 100 order and include the wrong USD 100 order, and the grand total still matches. List missing and extra IDs, then compare amounts for each order. Use only records you are permitted to inspect for this purpose.

The reference can be wrong too. Ask the people responsible for the number and its source to settle conflicting definitions or versions. Save both results, their differences, and the eventual decision. Don't edit the expected answer to match the query or publish commentary based on an unresolved number.

If an order changes after the reference was exported, the old report and new query now use different facts. Keep both versions and rerun the comparison on matching snapshots where permitted. Check source and calculation changes before blaming the model.

Other numbers have different addition rules. You cannot add daily balance snapshots to get a period's balance. To combine percentages, you may need their underlying counts rather than an average of the percentages. Document and test the rule for the number you actually need.

Keep a test that rejects both wrong answers

Keep the two-order example as a deliberately bad test case for each incorrect calculation. Both USD 500 and USD 100 must fail the expected USD 200 answer. Add separate cases with conflicting order amounts and missing line items. The correct response to those may be to stop for investigation rather than return a number.

For your next query, write down what one row means and which records belong. Ask who can supply a checked reference. Compare a small permitted set order by order, and save the differences. That small check does not verify the larger query until you apply the same rules to its actual records.