Unit 05.03: Pre-aggregating the many-side as the fix

Unit ID: SQL-M05-U04 - Estimated active time: 15-18 minutes Objective: combine order-level and item-level measures in one result without double counting.

The requirement that tempts the bug

"Show me revenue and item count together." Revenue lives at order grain; item count lives at item grain. Joining them naively inflates revenue, as the previous unit showed.

Fix 1 - do not join at all

If every measure you need is on one table, stay there:

SELECT COUNT(*) AS orders, SUM(order_total) AS revenue
FROM orders;
-- 1000 | 2701463.00

The best fix for a fan-out bug is frequently the join you did not write.

Fix 2 - collapse the many side first, then join

Aggregate order_items to one row per order before joining. The join becomes one-to-one and nothing is duplicated:

SELECT
  SUM(o.order_total) AS revenue,
  SUM(i.item_count)  AS items
FROM orders o
LEFT JOIN (
  SELECT order_id, COUNT(*) AS item_count
  FROM order_items
  GROUP BY order_id
) i ON i.order_id = o.order_id;
-- 2701463.00 | 3400

Both figures are now correct in a single result: ₹27,01,463 revenue and 3,400 items. Compare with the ₹91,47,789 from the naive join - same tables, same intent, one correct answer.

LEFT JOIN rather than JOIN matters here: it keeps orders that have no items instead of silently dropping them.

Fix 3 - the one to be careful with

SUM(DISTINCT order_total) is tempting and usually wrong:

SELECT SUM(DISTINCT o.order_total)
FROM orders o JOIN order_items i ON i.order_id = o.order_id;
-- 2700463.00

Close to the true ₹27,01,463 - and short by exactly ₹1,000. Orders 500 and 501 both total ₹1,000, so DISTINCT treats them as one value and discards a real order's revenue. It deduplicates *values*, not *rows*, and legitimate repeated amounts disappear.

A fix that is nearly right is worse than one that is obviously wrong, because it survives review.

Practice

Produce one result showing, per status: order count, revenue, and total items - all correct.

SELECT
  o.status,
  COUNT(*)                    AS orders,
  SUM(o.order_total)          AS revenue,
  SUM(COALESCE(i.item_count, 0)) AS items
FROM orders o
LEFT JOIN (
  SELECT order_id, COUNT(*) AS item_count
  FROM order_items
  GROUP BY order_id
) i ON i.order_id = o.order_id
GROUP BY o.status
ORDER BY revenue DESC;

Takeaway

Collapse the many side to the grain you need, then join. Reach for DISTINCT last - it removes values, not duplicates, and quietly deletes real data.

---

Mark complete