Why SUM is too big after a JOIN in SQL (rows fan out)

Why the usual answer is wrong

SELECT o.customer, SUM(o.amount) AS ordered, SUM(p.paid) AS paid
FROM orders o
JOIN payments p ON p.order_id = o.order_id
GROUP BY o.customer;

Order 1 was paid in two parts, so the join gives it two rows and its 500 is added twice. Asha shows 1,200 ordered instead of 700.

The answer

SELECT o.customer, SUM(o.amount) AS ordered, SUM(p.paid) AS paid
FROM orders o
LEFT JOIN (
  SELECT order_id, SUM(paid) AS paid FROM payments GROUP BY order_id
) p ON p.order_id = o.order_id
GROUP BY o.customer;

Add up payments per order first, so each order meets one row. Then the join can't repeat an order and its amount is counted once.

Before you SUM after a join, ask: can one row on the left match many on the right? If so, aggregate that side first.

SELECT o.customer, SUM(o.amount) AS ordered,
  (SELECT SUM(p.paid) FROM payments p
   JOIN orders x ON x.order_id = p.order_id
   WHERE x.customer = o.customer) AS paid
FROM orders o
GROUP BY o.customer;