Join Fan-Out (Double Counting) in SQL

Example query

This example assumes: customers(id bigint), orders(id bigint, customer_id bigint, total_amount numeric), tickets(id bigint, customer_id bigint). It is written in PostgreSQL syntax, and your table and column names will differ from these.

WITH order_totals AS (
  SELECT customer_id, SUM(total_amount) AS order_total
  FROM orders
  GROUP BY customer_id
),
ticket_counts AS (
  SELECT customer_id, COUNT(*) AS tickets
  FROM tickets
  GROUP BY customer_id
)
SELECT c.id,
       COALESCE(o.order_total, 0) AS order_total,
       COALESCE(t.tickets, 0) AS tickets
FROM customers c
LEFT JOIN order_totals o ON o.customer_id = c.id
LEFT JOIN ticket_counts t ON t.customer_id = c.id
ORDER BY order_total DESC;

The version that double counts (do not use)

-- A customer with 3 orders and 4 tickets produces 12 joined rows,
-- so each order total is added 4 times.
SELECT c.id,
       SUM(o.total_amount) AS order_total,
       COUNT(t.id) AS tickets
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
LEFT JOIN tickets t ON t.customer_id = c.id
GROUP BY c.id
ORDER BY order_total DESC;

How it works

• Each CTE reduces its table to one row per customer before any join happens.
• Joining one row per customer to one row per customer cannot multiply anything, so the totals are right.
• The 'do not use' version joins orders and tickets directly: every order is repeated for every ticket of the same customer, so SUM is inflated.

MySQL, BigQuery, SQL Server and SQLite differences

• The problem and the fix are the same in every database.

Common mistakes

• COUNT(DISTINCT ...) hides the fan-out for that column but SUM and plain COUNT stay inflated.
• A total that looks too high after adding a join is the classic symptom; compare it with a query on the single table.
• Joining on a non-unique key (for example email instead of id) causes the same effect.

Ask for it in plain English

Instead of writing the query yourself, you can type the question. SchemaChat turns it into SQL against your connected database or uploaded file, runs it, and shows the SQL and the figures it used so you can check the answer before you share it. Check that the query matches the definition your business uses.

• Show each customer's total order value and number of support tickets.
• Why is my revenue total higher after joining the tickets table?