One-to-many joins: reconcile revenue after a join

Joining an order to item lines is one one-to-many relationship. Joining the same order to refund events introduces another. If both detail tables are joined directly, their rows can multiply each other. The resulting error affects more than the order header: even the item and refund amounts may now be repeated.

The reliable pattern is to calculate each measure at its natural grain, summarize each detail table to the reporting key, and then join those summaries. Reconcile by key before trusting the grand total.

This lesson extends the row-grain introduction using the same synthetic commerce lab. Here the task is to report completed-order value, refunds and refund-adjusted value together.

Inspect a single order before aggregating everything

Order O1005 has two item lines: 10,000 and 15,000 paise. It also has two refund events: 5,000 and 2,500 paise. Its correct values are therefore:

A direct join creates every matching combination of item and refund rows:

SELECT i.line_id, r.refund_id,
       i.line_total_paise, r.refund_paise
FROM orders AS o
JOIN order_items AS i ON i.order_id = o.order_id
JOIN refunds AS r ON r.order_id = o.order_id
WHERE o.order_id = 'O1005'
ORDER BY i.line_id, r.refund_id;

There are four combinations, not two. Each item line appears once with each refund. Summing the displayed item values gives 50,000 paise; summing refunds gives 15,000. Both are doubled.

Adding DISTINCT is unsafe because equal values can belong to different legitimate events. The relationship must be resolved at the entity-key level. SQL's join rules describe how matches combine; they do not allocate business measures for you. PostgreSQL table expressions.

Aggregate each detail table independently

WITH item_totals AS (
    SELECT order_id, SUM(line_total_paise) AS item_value_paise
    FROM order_items
    GROUP BY order_id
), refund_totals AS (
    SELECT order_id, SUM(refund_paise) AS refund_value_paise
    FROM refunds
    GROUP BY order_id
)
SELECT
    o.order_id,
    o.order_total_paise,
    i.item_value_paise,
    COALESCE(r.refund_value_paise, 0) AS refund_value_paise,
    o.order_total_paise - COALESCE(r.refund_value_paise, 0)
        AS refund_adjusted_value_paise
FROM orders AS o
LEFT JOIN item_totals AS i ON i.order_id = o.order_id
LEFT JOIN refund_totals AS r ON r.order_id = o.order_id
WHERE o.status = 'completed'
ORDER BY o.order_id;

Each summary contains at most one row per order. Joining them to unique order headers preserves that grain. O1005 now shows the values in the reference table, and the eight completed orders produce 86,500 paise of refund-adjusted value.

COALESCE is used only for refunds. Under this fixture's contract, no refund record means no refund occurred. We leave a missing item summary as NULL because an order with no item data deserves investigation. Replacing it with zero would conceal an incomplete relationship.

Reconcile by order, not only by total

WITH item_totals AS (
    SELECT order_id, SUM(line_total_paise) AS item_value_paise
    FROM order_items
    GROUP BY order_id
)
SELECT o.order_id, o.order_total_paise, i.item_value_paise
FROM orders AS o
LEFT JOIN item_totals AS i ON i.order_id = o.order_id
WHERE i.order_id IS NULL
   OR i.item_value_paise <> o.order_total_paise
ORDER BY o.order_id;

The unchanged fixture returns no exceptions. This checks both missing item summaries and unequal totals. A grand total alone could miss two errors that cancel each other—for example, one order overstated by 1,000 paise and another understated by the same amount.

The lab's mutation check changes O1001's header amount and confirms that reconciliation identifies that order. This is evidence that the check can detect a seeded defect, not proof that it catches every possible data problem.

Keep time and eligibility consistent

Our exercise includes every refund attached to an eligible completed order. A real monthly report must decide whether it means refunds issued during the month or all refunds associated with that month's orders. Those populations differ when returns arrive later.

Similarly, item totals may exclude shipping or include tax differently from the header. Establish the source definitions before making equality a release condition. A failed check can reveal a true defect or an incorrectly specified expectation.

Record the treatment of partial refunds, cancelled orders, late events and unmatched records. Without that contract, two analysts can produce different totals while each believes they followed the request.

Turn the pattern into an assessment

Give a learner the direct three-table join and ask them to explain the inflated result for O1005. A complete answer should identify the two independent one-to-many relationships, show the four combinations, repair the query and demonstrate the corrected values.

Award credit for the diagnosis and validation, not just for producing a query that matches the grand total. Ask what would happen if two refund events had the same amount; that exposes whether the learner is relying on a distinct-value shortcut.

The Data Analytics with Generative AI programme connects SQL, pipelines and reporting. This reconciliation pattern is a practical bridge between them: it lets an analyst preserve the meaning of monetary measures as data moves through increasingly complex transformations.

Continue learning

This article is part of the SQL foundations for reliable analysis sequence. Use the neighbouring tasks when you need the prerequisite or the next application.

• Review the prerequisite or neighbouring task in Find missing records with SQL anti-joins.
• Continue with GROUP BY and HAVING for meaningful customer segments.