Wrong SQL Results Without Errors
A join that fans out can silently inflate your counts and sums. No error, just a wrong answer.
The most dangerous queries are the ones that look fine. They run without complaint and hand back a confident-looking number that happens to be wrong. The classic culprit is a fan-out join.
When you join orders to order_items, each order is repeated once per line item. Now count(*) counts line items, not orders, so the totals come out too high. The fix is to count the thing you actually mean.
-- fan-out join inflates count(*):
count(*) -- counts joined rows (line items)
-- count the thing you actually mean:
count(distinct o.order_id)Schema · Garden ShopTable · order_items5 columns · 48 rows
One row per line item within an order.
The starter reports inflated order counts because of theorder_items join. Fix it so each customer'sorder_count is the true number of orders they placed. Keep the customers sorted by customer_id.
- Columns: customer_id, order_count.
- Rows: 16 customers who have placed orders.
- count(distinct o.order_id) gives the true order count even with the order_items join.
Related
Use error messages as clues.
Shrink a query and check one piece at a time.
Make queries easier to scan and debug.
Paste an error message and find the fix, from GROUP BY to constraint failures.