Why Your SQL JOIN Doubled the Total—and How to Fix It

The orders total 240. The joined report says 420. The database added the numbers correctly. We gave it the wrong rows to add.

A join can change what one row represents. Order totals belong to orders; item details belong to items. Put them together carelessly and the same order total appears once for every item.

Explore the idea

Watch the total grow without a new sale

The same three orders take two different routes through the join.

420

incorrect order revenue

Each matching item repeats its order total

100 appears twice, 60 once, and 80 twice. The join has five rows because it describes order–item pairs.

Two tables, two meanings of a row

There are three purchases and five item rows. Ada’s order totals 100, Ben’s 60, and Cy’s 80. The line totals happen to reconcile with their orders, but they remain different measures at different levels.

orders3 rows

Orders: one row per purchase

The three order totals add up to 240. Amounts are whole currency units in this fictional example.

id	customer	order_total
101	Ada	100
102	Ben	60
103	Cy	80

items5 rows

Items: several rows per purchase

An order total belongs to the order. A line total belongs to one item row.

id	order_id	sku	line_total
1	101	MUG	60
2	101	TEA	40
3	102	TEA	60
4	103	BOWL	50
5	103	SPOON	30

Run along, with the data included

Each Run example button opens an editable SQL shell with its own copy of this sample data. Run the examples in any order. The engine downloads on your first run; sample queries execute in your browser.

Create these tables yourself: full setup SQL

CREATE OR REPLACE TABLE orders(id INTEGER, customer VARCHAR, order_total INTEGER);
INSERT INTO orders VALUES (101,'Ada',100),(102,'Ben',60),(103,'Cy',80);
CREATE OR REPLACE TABLE items(id INTEGER, order_id INTEGER, sku VARCHAR, line_total INTEGER);
INSERT INTO items VALUES
  (1,101,'MUG',60),(2,101,'TEA',40),(3,102,'TEA',60),
  (4,103,'BOWL',50),(5,103,'SPOON',30);

Download the complete SQL walkthrough

Before calculating anything, look at the joined records:

See the repeated order totals

SELECT o.id, i.sku, o.order_total
FROM orders o JOIN items i ON i.order_id = o.id
ORDER BY o.id, i.id;

Expected result · sample data5 rows

id	sku	order_total
101	MUG	100
101	TEA	100
102	TEA	60
103	BOWL	80
103	SPOON	80

Ada’s 100 appears twice. Ben’s 60 appears once. Cy’s 80 appears twice. The join is doing precisely what its condition requests: returning each matching order–item pair.

The aggregate that looks reasonable

Now sum the order total over those pairs:

Sum the expanded result

SELECT sum(o.order_total) AS revenue
FROM orders o JOIN items i ON i.order_id = o.id;

Expected result · sample data1 row

revenue
420

The result is 100 + 100 + 60 + 80 + 80 = 420. Adding a GROUP BY customer would not fix the duplication; it would merely place the repeated amounts into customer groups.

SUM(DISTINCT order_total) is not a general repair either. Two different orders can legitimately have the same total. Deduplicating amounts would then erase a real purchase. The order’s identity is its key, not its price.

Bring both sides to one row per order

Suppose the report needs an order total, an item count, and an item subtotal. Aggregate the item table by order_id first, then join that result to orders:

Aggregate items before joining

WITH item_totals AS (
  SELECT order_id, count(*) AS item_count, sum(line_total) AS item_total
  FROM items GROUP BY order_id
)
SELECT o.id, o.customer, o.order_total, i.item_count, i.item_total
FROM orders o LEFT JOIN item_totals i ON i.order_id = o.id
ORDER BY o.id;

Expected result · sample data3 rows

id	customer	order_total	item_count	item_total
101	Ada	100	2	100
102	Ben	60	1	60
103	Cy	80	2	80

Scroll for more columns →

The right side now has at most one row per order ID. Combined with the unique orders.id in this fixture, the join preserves one row per order. The left join would also retain an order with no items, leaving its item summaries NULL. Decide whether those mean missing data or should be displayed as zero.

If all you need is the overall order revenue, query the orders table directly:

Count each order once

SELECT sum(order_total) AS revenue FROM orders;

Expected result · sample data1 row

revenue
240

There is no need to join another table merely because it is available. Join when its columns or its existence change the question you need to answer.

Filtering by items without multiplying orders

“Revenue from orders with at least two items” needs information from both tables. First identify qualifying order IDs; then sum each matching order once. IN or EXISTS can express that eligibility test without returning every item pair.

Your turn · check your answer

Total the orders with at least two items

Return one column, revenue, summing order_total once for each order that has at least two item rows.

See the expected result

Your target result1 row

revenue
180

Your SQL

Sample data is loaded for you.

Preparing the editor…

Need a hint?

Find qualifying order IDs first, then filter orders with IN or EXISTS. Do not sum the expanded item join.

Show a solution

SELECT sum(order_total) AS revenue
FROM orders
WHERE id IN (
  SELECT order_id FROM items
  GROUP BY order_id HAVING count(*) >= 2
);

A small checklist for a suspicious total

Inspect a few joined keys before running the full aggregate. Count matches per key on each side. State what one result row should mean, and verify that the join preserves it. A relationship that is one-to-many in today’s sample can become many-to-many when a supposedly unique lookup table gains duplicates.

Pre-aggregation is useful when you need summarized detail columns. An existence test is useful when you only need to know whether a related record qualifies. Neither removes the need to check the key assumptions.

For a larger query, EXPLAIN ANALYZE can reveal the same expansion in actual operator row counts. Once you recognize the pattern here, a join producing twice its input is a concrete lead to investigate.