Watch the tutorial
Watch the full SQL JOIN and SUM tutorial or the short introduction.
Two orders total $200. After joining them to their items, a query reports $300 — with no error. Add DISTINCT inside SUM, and the answer becomes $100. Both results can be wrong for the question we intended to ask.
Let's inspect the rows, reproduce the problem in PostgreSQL and choose a fix based on which orders should count. The example is synthetic and read-only.
The question comes first
Suppose we need the sum of order totals for orders with at least one item. We have two orders:
| order_id | order_total |
|---|---|
| 101 | 100 |
| 102 | 100 |
The items are:
| item_id | order_id |
|---|---|
| 1 | 101 |
| 2 | 101 |
| 3 | 102 |
Each order appears once in the orders data, and both have at least one item. The intended total is 100 + 100 = 200. In this demonstration, an order total is an order-level amount; the item table identifies matches and is not a source of item prices.
Each runnable example below defines its input using WITH and VALUES. Run a complete block in pgAdmin's Query Tool or another PostgreSQL client. It does not create persistent tables or modify data.
Start with the order-level total
WITH orders(order_id, order_total) AS (VALUES (101,100), (102,100))
SELECT SUM(order_total) AS order_total
FROM orders;
For these inputs the result is 200. Since both orders have items, the total of all orders happens to equal the total of orders with at least one item. That will not always be true.
Look at what JOIN really produces
WITH orders(order_id, order_total) AS (VALUES (101,100), (102,100)),
items(item_id, order_id) AS (VALUES (1,101), (2,101), (3,102))
SELECT o.order_id, o.order_total, i.item_id
FROM orders AS o
JOIN items AS i ON i.order_id = o.order_id
ORDER BY o.order_id, i.item_id;
| order_id | order_total | item_id |
|---|---|---|
| 101 | 100 | 1 |
| 101 | 100 | 2 |
| 102 | 100 | 3 |
The join returns one row per matching order and item pair. Order 101 has two matching items, so its order-level amount appears twice. These rows describe distinct order-item pairs, not duplicate items or duplicate orders in the original input. See PostgreSQL's explanation of joined tables.
SUM adds the joined rows
WITH orders(order_id, order_total) AS (VALUES (101,100), (102,100)),
items(item_id, order_id) AS (VALUES (1,101), (2,101), (3,102))
SELECT SUM(o.order_total) AS joined_sum,
SUM(DISTINCT o.order_total) AS distinct_amount_sum,
COUNT(*) AS joined_rows,
COUNT(DISTINCT o.order_id) AS distinct_order_ids
FROM orders AS o
JOIN items AS i ON i.order_id = o.order_id;
| joined_sum | distinct_amount_sum | joined_rows | distinct_order_ids |
|---|---|---|---|
| 300 | 100 | 3 | 2 |
SUM(o.order_total) sums the order total on every joined row: 100 + 100 + 100 = 300. The extra $100 comes from repeating order 101's total once for each item.
SUM(DISTINCT o.order_total) does something different: it sums each distinct amount once, not each order once. Both orders have the amount 100, so this query counts that amount only once and returns 100. Distinct amounts are useful if that is actually the question; they are not a general repair for counting orders. PostgreSQL describes DISTINCT within aggregates in its aggregate-expression documentation.
COUNT(DISTINCT o.order_id) is a helpful diagnostic in this specific example because order_id identifies orders in the supplied data: three matched pairs contain two order IDs. A row-count difference does not automatically mean a join is wrong — it can be expected for a one-to-many relationship.
Fix for “orders with at least one item”: EXISTS
If we only need to check that a matching item exists, EXISTS asks that question without producing one order row per item:
WITH orders(order_id, order_total) AS (VALUES (101,100), (102,100)),
items(item_id, order_id) AS (VALUES (1,101), (2,101), (3,102))
SELECT SUM(o.order_total) AS matched_order_total
FROM orders AS o
WHERE EXISTS (
SELECT 1 FROM items AS i WHERE i.order_id = o.order_id
);
The result is 200. EXISTS tests whether the subquery returns a row for the current order; its SELECT 1 does not mean that it returns or counts item prices. It includes an order once if at least one matching item exists. See PostgreSQL's EXISTS documentation.
If your report also needs item-level values, you may instead aggregate the item side to one row per order before joining it to orders. Joining a one-row-per-order summary avoids multiplying the order-level amount, provided the order key is unique and the joins and filters reflect the intended population. Whether an order amount should equal the sum of its item amounts depends on business rules such as discounts, tax and shipping; do not assume it without checking.
What about orders with no items?
Add an order 103 worth 50 with no matching item. The total of all orders is now 250. The total of orders with at least one item remains 200. Neither number answers every question:
WITH orders(order_id, order_total) AS (VALUES (101,100), (102,100), (103,50)),
items(item_id, order_id) AS (VALUES (1,101), (2,101), (3,102)),
item_counts AS (
SELECT order_id, COUNT(*) AS item_count
FROM items
GROUP BY order_id
)
SELECT SUM(o.order_total) AS all_order_total,
SUM(o.order_total) FILTER (WHERE ic.order_id IS NOT NULL) AS matched_order_total
FROM orders AS o
LEFT JOIN item_counts AS ic ON ic.order_id = o.order_id;
For this input the query gives 250 and 200 respectively. The grouped item_counts has at most one row per order ID; the LEFT JOIN retains order 103. Its unmatched ic.order_id is NULL, so the filtered aggregate excludes it. An inner join or the earlier EXISTS query intentionally excludes the order with no items. Choose based on your report's requirements.
With no qualifying non-null input amounts, PostgreSQL's SUM returns NULL rather than zero. If zero is the desired reporting rule, use COALESCE deliberately. See aggregate functions.
A practical debugging checklist
- Write down what one row represents in each input and what the final metric must count.
- Inspect the joined rows before aggregating, including identifiers from both sides.
- Compare joined row counts with distinct order IDs when the key is known and non-null.
- Check whether missing matches should be retained or removed.
- Compare against a small dataset with known answers, including two different orders that have the same amount.
These are correctness checks, not a performance benchmark. The best execution plan and timing depend on data, indexes, PostgreSQL version and the actual query; measure the relevant workload rather than assuming that EXISTS is always faster.
Try it yourself
Add a third item to order 101. What does the naive joined sum return? What does the EXISTS query return? Then add an order with no items and decide explicitly whether your report should include it. Run the queries and compare the results.
For another join-related surprise, see Why Your LEFT JOIN Loses Rows: ON vs WHERE in PostgreSQL.
For guided SQL practice, explore Practical SQL with PostgreSQL: From Queries to Real Projects. This is the instructor's referral link. The course uses TrainingHub, a synthetic training-business database.
The example uses self-contained PostgreSQL SELECT statements with synthetic data. Verify results in your own environment and on representative inputs.








