SQL Join Practice: Catch AI Double-Counting Errors

A SQL join can run successfully and still inflate a total. Before trusting a generated query, state what one row represents in each input table and in the requested result. This exercise joins orders to order items. An order with two items appears twice after the join, so summing its order-level total repeats the same amount.

Load the original fixture

Download the runnable SQL exercise. Run it in a disposable SQLite database with sqlite3 :memory: < sql-joins.sql, or execute the statements in a fresh PostgreSQL practice database. The fixture creates three small tables. Do not run teaching setup files in a production database.

Customers are Ada, Ben and Cy. Ada has orders 10 and 11, worth 100 each. Ben has order 12, worth 50. Cy has no orders. Order 10 has two item rows; orders 11 and 12 have one item row each. The correct customer-level totals are Ada 200, Ben 50 and Cy 0. Notice that Ada has two different orders with the same amount.

Explain the broken query

SELECT o.customer_id, SUM(o.total) AS revenue
FROM orders o
JOIN order_items i ON i.order_id = o.id
GROUP BY o.customer_id;

For Ada, the query adds order 10 twice and order 11 once, returning 300. Ben returns 50. The combined total becomes 350 instead of 250. No database error is raised because the query is valid SQL. The mistake is a mismatch between the level of the amount and the level of the joined rows.

Do not repair it with SUM(DISTINCT o.total). Ada's two legitimate 100-value orders would collapse into one value, producing 100 instead of 200. Deduplicating numeric values is not the same as deduplicating order identities.

Use the table that matches the question

SELECT c.name, COALESCE(SUM(o.total), 0) AS revenue
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name
ORDER BY c.id;

The requested result needs order totals, so no item join is necessary. The left join keeps Cy, and the example uses zero for a customer with no orders. PostgreSQL's join tutorial explains matching rows and the preservation of unmatched left-side rows. The fixture and numerical trap here are original.

If the real question requires an item filter, first identify the qualifying order IDs or use an EXISTS condition. Do not introduce the item rows into an order-level sum unless you deliberately restore one row per order first. If the question asks for item revenue instead, calculate from item amounts and define how discounts are allocated.

Check more than the grand total

The download prints the broken result, the distinct-value trap and the corrected result. It also includes a grouped-order alternative so you can compare approaches. Hand-check each customer's rows before looking at the final sum.

Turn it into a learning exercise

Ask an AI assistant why the item join changes row count, then demand an explanation using order IDs rather than a generic warning about duplicates. Add a third item to order 10 and predict which queries change. The correct order-level revenue stays 250. Continue with SQL window functions or the introductory SQL workflow.

Sources and further practice

PostgreSQL: Joins Between Tables supports the reference principle used here. The exercise, example data and review routine on this page are original Paradigm Study teaching examples.

For a broader workflow, see coding learners. Bring your attempt and the step that confused you into Paradigm Study for a lesson or focused practice. Start a learning notebook.