AI wrote SQL that runs. Why did the order total increase after a JOIN?

A one-to-many join can repeat an order’s total for each matching item. Check what one row represents before summing it.

A query can execute without calculating the intended amount. SQLite’s SELECT documentation describes how joins combine matching rows. When one order matches several items, its order-level total can appear several times in the joined result.

A small test makes the error visible

We tested fictional data in an in-memory SQLite database. Orders 1 and 2 total 10,000 and 20,000 won; order 1 has two items and order 2 has one. SELECT SUM(o.total) FROM orders o JOIN items i ON i.order_id=o.id; returns 40,000, although the two orders total 30,000.

Choose the unit you mean to count

If the question is “total of orders with at least one item”, our tested query is SELECT SUM(o.total) FROM orders o WHERE EXISTS (SELECT 1 FROM items i WHERE i.order_id=o.id); It returns 30,000 for that fixture. It counts each qualifying order once; it is not a universal repair for other business questions.

DISTINCT on the amount is not a general fix

In a second fixture, two different orders each total 10,000 won. SUM(DISTINCT o.total) over their joined rows returns 10,000, not the intended 20,000. Equal amounts do not mean the orders are duplicates.

Ask AI to explain the row before the formula

Ask it to state the unit represented by each input and output row, explain each join’s relationship, and test multiple items, equal totals and missing matches. Check against a hand-calculated fixture before adapting any query to real data.

Published October 14, 2026. The two fixtures and stated SELECT results were executed locally with Python’s SQLite module using only :memory:. No production database or AI coding service was accessed; other SQL dialects and business rules were not tested.

Sources: SQLite · SELECT.