The JOIN that multiplies the sum

The problem

You have an orders table, with the amount of each order, and an items table, with one row for each item purchased. An order can have several items: it is a one-to-many relationship.

When you join the two with JOIN and sum the order amount, each order shows up once for each item. The sum inflates even though nobody sold anything extra.

All the numbers in this guide come from a fictional example.

Build the example

Two orders: order 1 is worth 100 and has 3 items; order 2 is worth 50 and has 1 item.

The code keeps the table and column names in Portuguese, so it is the same in both versions of this guide: pedidos means orders, itens means items, valor means amount, pedido_id means order_id and qtd_itens means item count.

CREATE TABLE pedidos (id int PRIMARY KEY, valor int);
CREATE TABLE itens (pedido_id int);
INSERT INTO pedidos VALUES (1, 100), (2, 50);
INSERT INTO itens VALUES (1), (1), (1), (2);

Watch the sum inflate

The correct sum of the orders:

SELECT sum(valor) FROM pedidos;

The same sum after the JOIN with the items:

SELECT sum(p.valor)
FROM pedidos p
JOIN itens i ON i.pedido_id = p.id;

The order worth 100 shows up three times, once per item: 100 + 100 + 100 + 50 = 350.

Why it happens

The PostgreSQL documentation, in section 7.2.1.1 Joined Tables, explains that a JOIN produces one row for each row of the other table that satisfies the condition. If an order matches three items, it becomes three rows.

The JOIN is doing what it should. The mistake is summing a column from the "one" side after the "many" side has multiplied the rows.

Fix 1: aggregate the items before the JOIN

Summarize the items table so it has one row per order, and only then do the JOIN.

SELECT sum(p.valor)
FROM pedidos p
JOIN (
  SELECT pedido_id, count(*) AS qtd_itens
  FROM itens
  GROUP BY pedido_id
) a ON a.pedido_id = p.id;

Use this approach when you need item information in the result, such as the number of items per order.

Fix 2: only need to know whether there is an item? Use EXISTS

If the question is just "does this order have any item?", you do not need to join the tables. EXISTS tests the condition without repeating the order.

SELECT sum(valor)
FROM pedidos p
WHERE EXISTS (
  SELECT 1 FROM itens i WHERE i.pedido_id = p.id
);

Check the rows before and after

Before you trust a sum after a JOIN, compare the number of rows:

SELECT count(*) FROM pedidos;

SELECT count(*)
FROM pedidos p
JOIN itens i ON i.pedido_id = p.id;

If you expected one row per order and the number went up, some row was multiplied.

Order with no items? LEFT JOIN with the aggregated items

Now add order 3, worth 20, with no items at all. The correct sum becomes 170.

INSERT INTO pedidos VALUES (3, 20);

A plain JOIN and EXISTS leave order 3 out and return 150. LEFT JOIN keeps every order, but it still needs the aggregated items:

SELECT sum(p.valor)
FROM pedidos p
LEFT JOIN (
  SELECT pedido_id, count(*) AS qtd_itens
  FROM itens
  GROUP BY pedido_id
) a ON a.pedido_id = p.id;

Source

Official PostgreSQL 16 documentation, Table Expressions page, sections 7.2.1.1 Joined Tables and 7.2.2 The WHERE Clause: postgresql.org/docs/16/queries-table-expressions.html. Checked on November 7, 2026.