Lesson 9.3

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 usual 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 first fix to reach for is the join itself. If the question doesn't need anything from order_items, drop it, and the rows stop multiplying. When you do need it, say for units bought alongside the order count, keep the join and count the thing you actually mean.

Pattern

-- fan-out join inflates count(*):
count(*)                 -- counts joined rows (line items)

-- count the thing you actually mean:
count(distinct o.order_id)

Schema · Garden Shop Tables · order_items, customers, orders 3 tables

Table · order_items

One row per line item within an order.

5 columns · 48 rows

order_item_id int order_id int product_id int quantity int unit_price decimal

Table · customers

One row per customer. Some customers have no phone on file.

9 columns · 20 rows

customer_id int first_name text last_name text email text phone text city text state text signup_date date is_active bool

Table · orders

One row per order. Unshipped orders have a null shipped_date.

6 columns · 24 rows

order_id int customer_id int order_date date shipped_date date status text coupon_code text

Your task

The starter reports each customer's order_count and units_bought (the total quantity across their line items), but the order counts are inflated by the order_items join. Fix order_count so it is the true number of orders they placed, and keep units_bought as it is. Keep the customers sorted by customer_id.

Garden Shop · SQL engine loading · Ctrl/⌘ + Enter to run·

Run your query to inspect the result.

Expected answer

Columns: customer_id, order_count, units_bought.
Rows: 16 customers who have placed orders.
Customer 1 placed 3 orders and bought 8 units; count(*) would have said 6 orders.

Previous · 9.2 Debugging in small steps

Next · 9.4 Formatting for readability →

Reference

SQL Query Formatting Guide

Indentation, casing, and aliasing conventions.

Mistake

COUNT or SUM Too High After a JOIN

One-to-many joins can inflate totals.

Error

SQL Error Messages Explained

Paste an error message and find the fix, from GROUP BY to constraint failures.

Mistake