The totals doubled when payments were added
An order summary showed the value of each order's items. Someone added a column for how much has been paid, joined the payments table, and every total on the page doubled — including the item totals, which nobody touched.
Return one row per order with the item total and the amount paid, both correct.
Example
- input
orders(id, customer), items(id, order_id, qty, price), payments(id, order_id, amount)outputid, item_total, paidOne row per order, ordered by id. An order with no items or no payments shows 0, not NULL.
Constraints
- Every order appears, even with no items and no payments.
- item_total is SUM(qty × price); paid is SUM(amount). Neither may be affected by how many rows the other table has.
- No window functions — this is about the shape of the join.
Hints
Hint 1
Count the rows the join produces for an order with 2 items and 2 payments. It is not 2.
Hint 2
Joining two child tables to one parent multiplies them: every item is paired with every payment, so each sum is repeated as many times as the other table has rows.
Hint 3
Aggregate each child table down to one row per order BEFORE joining it.
Stuck? The lesson behind this problem: 🐘 Aggregation and grouping
sql
Tab indents · Escape first to tab out
Test cases
These are the specification. Run tests checks your answer against them.
| Case | Input | Expected |
|---|---|---|
| two items and two payments | 1 order, 2 items, 2 payments | 25 and 25, not 50 and 50 |
| items but no payment | 1 order, 1 item | 21 and 0 |
| an order with neither | 2 orders, one empty | 0/0 and 4/4 |