Return payment and refund totals per order without double-counting when an order has several rows in both tables.

Tables: Orders(OrderId, CreatedAtUtc), Payments(PaymentId, OrderId, Amount, Status), and Refunds(RefundId, OrderId, Amount, Status). For orders in a requested range, return captured payment total, completed refund total, and net paid amount.

An order has two captured payments and three completed refunds. Joining both detail tables directly would create six rows and repeat amounts. The result must sum each detail table once.

• Only captured payments and completed refunds contribute.
• Orders with no payments or refunds still return zero totals.
• Use decimal arithmetic for money.
• Filter orders by a half-open creation range.

I aggregate payments to one row per order and refunds to one row per order before joining either result to Orders. Once both child sets have the same grain as the parent, their totals cannot multiply each other. I use COALESCE only after the joins so missing child groups become zero.

WITH PaymentTotals AS
(
    SELECT p.OrderId, SUM(p.Amount) AS PaidAmount
    FROM dbo.Payments AS p
    WHERE p.Status = 'Captured'
    GROUP BY p.OrderId
),
RefundTotals AS
(
    SELECT r.OrderId, SUM(r.Amount) AS RefundedAmount
    FROM dbo.Refunds AS r
    WHERE r.Status = 'Completed'
    GROUP BY r.OrderId
)
SELECT
    o.OrderId,
    COALESCE(p.PaidAmount, CONVERT(decimal(18, 2), 0)) AS PaidAmount,
    COALESCE(r.RefundedAmount, CONVERT(decimal(18, 2), 0)) AS RefundedAmount,
    COALESCE(p.PaidAmount, CONVERT(decimal(18, 2), 0))
      - COALESCE(r.RefundedAmount, CONVERT(decimal(18, 2), 0)) AS NetPaidAmount
FROM dbo.Orders AS o
LEFT JOIN PaymentTotals AS p ON p.OrderId = o.OrderId
LEFT JOIN RefundTotals AS r ON r.OrderId = o.OrderId
WHERE o.CreatedAtUtc >= @FromUtc
  AND o.CreatedAtUtc < @ToUtc
ORDER BY o.OrderId;

Each child table is scanned or sought once and reduced to one row per order before the final joins. On large tables, filter child aggregates to the relevant order set or provide indexes on (OrderId, Status) including Amount so the report does not aggregate unrelated history.

• Several payments and several refunds for one order.
• An order with refunds but no captured payment due to imported history.
• Failed payment and pending refund rows.
• A partially refunded order.

• Use at least two rows in each child table to expose multiplication.
• Include an order with neither child type.
• Mix contributing and non-contributing statuses.
• Reconcile the net amount to independently computed child totals.

• Joining both detail tables and grouping only at the end.
• Using DISTINCT on monetary values and losing legitimate equal payments.
• Forgetting orders with no child rows.
• Computing financial totals without a reconciliation check.