Vì sao JOIN nhiều bảng rồi `SUM` lại ra số lớn gấp đôi? Xử lý fan-out thế nào?

-- orders(1) -> order_items(n) va orders(1) -> payments(m)
-- moi order bi nhan len n*m dong
SELECT o.id, SUM(i.qty), SUM(p.amount)
FROM orders o
JOIN order_items i ON i.order_id = o.id
JOIN payments   p ON p.order_id = o.id
GROUP BY o.id;   -- SUM(p.amount) bi thoi len n lan

-- 1. tong hop TRUOC khi join (tot nhat, ro rang)
SELECT o.id, i.qty_total, p.paid_total
FROM orders o
LEFT JOIN (SELECT order_id, SUM(qty) qty_total FROM order_items GROUP BY order_id) i
  ON i.order_id = o.id
LEFT JOIN (SELECT order_id, SUM(amount) paid_total FROM payments GROUP BY order_id) p
  ON p.order_id = o.id;

-- 2. subquery vo huong khi chi can 1 gia tri
SELECT o.id, (SELECT SUM(amount) FROM payments WHERE order_id = o.id) AS paid_total
FROM orders o;

-- 3. COUNT(DISTINCT ...) khi chi dem, khong cong
SELECT o.id, COUNT(DISTINCT i.id) FROM orders o JOIN order_items i ON ...;