---
title: "Join Fan-Out: Why Your SQL Total Is Silently Wrong"
canonical: "https://www.owox.com/blog/articles/join-fan-out-wrong-totals"
updated: "2026-11-02"
---

# Why Your Totals Are Silently Wrong after a Join: Finding Fan-Out before the Number Ships

The query runs, the total looks reasonable, and it is wrong. This guide is for the analyst who just added a join: what fan-out is, the version you will not notice by eye, why DISTINCT and a different join type hide it instead of fixing it, how a LEFT JOIN plus WHERE quietly becomes an inner join, and a three-step check to run before any joined total ships, with runnable SQL and figures from a demo retail chain.

[Data Modeling](/blog/topics/data-modeling) · Updated November 2, 2026 · 10 min read

![Why Your Totals Are Silently Wrong after a Join: Finding Fan-Out before the Number Ships](https://cdn.owox.com/www/content-marketing-article/join-fan-out-wrong-totals/thumbnail/1-v0ybh5.png/public)

“This query runs fine and the total is wrong. Where?” Almost always in a join that changed the grain: one row on the left met several rows on the right, and every one of them was summed. No error, no warning, and **a total that is wrong in a way that looks right**. The fix is not to add `DISTINCT` or to try another join type until the number looks familiar. It is a three-step check that takes a minute and catches it before the number ships:

1.  **Name the grain of every table in the join**: what one row is.
2.  **Prove the key on the “one” side is unique** for the join you wrote, not the one you meant.
3.  **Reconcile**: row count and the total before the join against after it.

The rest of this guide shows each failure on a year of demo data from a retail chain, then the check as SQL you can paste.

## What fan-out is, in one number

The demo chain has a sale-line table, one row per item rung through a till, and a store-traffic table, one row per store per day with the number of people who walked in. Join them on store and day, sum footfall, and October’s 247,866 visitors become **2,097,834,152**. Every sale line carried its day’s footfall with it, so each day’s count was summed once per line sold.

Nobody ships that number, because it is absurd. The ones that ship are the plausible ones.

## The fan-out you will not notice

Promotions are the classic. A sale line belongs to a category; a promotion covers a category for a date range; promotions overlap. Ask “how much did we sell in categories that were on promotion” and the natural query joins sales to promotions on category and date:

| October, demo chain | Sale lines | Net sales |
| --- | --- | --- |
| All sales | 1,046,367 | $10,735,229 |
| Joined to promotions on category and date | 538,760 rows | **$5,709,835** |
| Lines in a promoted category, counted once | 386,268 lines | **$4,077,972** |

_Demo data: the synthetic chain behind the free Retail Chain data model (`owox-demo.retail_chain`), October 2026, run on 2026-11-02._

**The joined total is 40% too high, and nothing about it looks wrong.** It is less than total sales. It has the right number of digits. A line sold while two promotions covered its category was counted twice, and a reviewer who did not know the promotional calendar could not tell. This is the “total wrong with correct join keys and no visible duplicates” case: the keys are correct, the grain is not.

![Join fan-out: four sale lines joined to promotions on category and date range. Line L2, Dairy on October 10, matches both dairy promotions P1 and P2, so it comes out twice; the sum after the join is $190, the sum with each line counted once is $130](https://cdn.owox.com/www/content-marketing-article/join-fan-out-wrong-totals/body/1-nytyjd.png/public)

## The Turning Point

OWOX Data Marts

See your first report built in real time. _15 minutes._

1.  Connect your data warehouse
2.  Pick your metrics
3.  Get a live Google Sheets report

In the time it takes to write a ticket. Then imagine never writing that ticket again.

[Book a Demo](/book-a-call)

We'll use your actual use case

## Why DISTINCT is not the fix

The first instinct is `SUM(DISTINCT ...)`. It removes the inflation in a toy example and appears to work. On the footfall join above:

| October footfall, demo chain | Visitors |
| --- | --- |
| From the traffic table | 247,866 |
| Joined to sale lines, `SUM(footfall)` | 2,097,834,152 |
| Joined to sale lines, `SUM(DISTINCT footfall)` | **241,090** |

**`SUM(DISTINCT)` is 2.7% low**, close enough to pass a glance and wrong. It adds up distinct _values_, not distinct _rows_: two store-days that happened to have the same footfall count once. On a column of money or counts, equal values on different rows are normal, so the error is random in size and always downward.

So when is `SUM(DISTINCT)` safe? Only when you actually want the sum of the distinct values, which is almost never a business question. Two neighbours that _are_ safe:

*   **`COUNT(DISTINCT key)`** where `key` identifies the thing you are counting (`basket_id` for baskets, `customer_id` for customers). Duplicated rows carry the same key, so they count once.
*   **Aggregating each table to the shared grain before joining.** Then there is nothing to de-duplicate.

`SELECT DISTINCT` on the joined rows has the same weakness: two genuinely different rows with identical values collapse into one. Changing `JOIN` to `LEFT JOIN` or the other way round does not touch fan-out at all; it changes which unmatched rows survive.

## LEFT JOIN plus WHERE is an inner join

The other silent failure goes the other way: rows disappear. You want October’s sales excluding lines returned because the shopper changed their mind, so you left-join returns and filter:

```
SELECT SUM(s.net_sales)
FROM pos_sales s
LEFT JOIN returns r ON r.sale_id = s.sale_id
WHERE r.reason != 'changed_mind'
```

On the demo chain’s October that returns **$117,033 instead of $10,670,404**. Most sale lines have no return, so `r.reason` is `NULL`, and `NULL != 'changed_mind'` is not true, so the `WHERE` drops them. The `LEFT JOIN` became an inner join. A condition on the right-hand table belongs in the `ON` clause, and “has no such return” is tested with `IS NULL`:

```
SELECT SUM(s.net_sales)
FROM pos_sales s
LEFT JOIN returns r
  ON r.sale_id = s.sale_id
  AND r.reason = 'changed_mind'
WHERE r.return_id IS NULL
```

Here the drop is 99% and someone would notice. When the right-hand column is usually filled, say a status set on most rows, only the unmatched rows go, the loss is small, and nobody does.

The returns join is also the example of a join that is safe: in the demo data no sale line has more than one return, so the row count before and after the join is the same, 1,046,367. **That equality is the evidence.** “It should be one-to-one” is not.

## The check to run before the number ships

Run this beside any query that sums across a join. It answers the three questions in one result: how many left-hand rows matched, how many rows the join produced, how many left-hand rows came out more than once, and the total both ways. The example is the promotion case in miniature, literal rows, so it runs in any BigQuery project as written:

```
WITH sales AS (
  SELECT * FROM UNNEST([
    STRUCT('L1' AS line_id,
      'Dairy' AS category,
      DATE '2026-10-03' AS sale_date,
      40.00 AS net_sales),
    ('L2', 'Dairy', '2026-10-10', 60.00),
    ('L3', 'Bakery', '2026-10-10', 30.00),
    ('L4', 'Frozen', '2026-10-10', 70.00)
  ])
),
promotion AS (
  SELECT * FROM UNNEST([
    STRUCT('P1' AS promotion_id,
      'Dairy' AS category_scope,
      DATE '2026-10-01' AS start_date,
      DATE '2026-10-14' AS end_date),
    ('P2', 'Dairy', '2026-10-08', '2026-10-21'),
    ('P3', 'Bakery', '2026-10-01', '2026-10-31')
  ])
),
joined AS (
  SELECT s.*, p.promotion_id
  FROM sales s
  JOIN promotion p
    ON p.category_scope = s.category
    AND s.sale_date
      BETWEEN p.start_date AND p.end_date
)
SELECT
  (SELECT COUNT(DISTINCT line_id) FROM joined)
    AS lines_matched,
  (SELECT COUNT(*) FROM joined)
    AS rows_after_join,
  (SELECT COUNT(*) FROM (
    SELECT line_id FROM joined
    GROUP BY line_id
    HAVING COUNT(*) > 1)) AS lines_repeated,
  (SELECT SUM(net_sales) FROM joined)
    AS total_after_join,
  (SELECT SUM(net_sales) FROM sales s
    WHERE EXISTS (
      SELECT 1 FROM promotion p
      WHERE p.category_scope = s.category
        AND s.sale_date
          BETWEEN p.start_date AND p.end_date))
    AS total_at_the_sales_grain
```

| lines\_matched | rows\_after\_join | lines\_repeated | total\_after\_join | total\_at\_the\_sales\_grain |
| --- | --- | --- | --- | --- |
| 3 | 4 | 1 | 190 | 130 |

**The verdict is in the first three columns.** Three lines matched and the join produced four rows, so one line, L2, came out twice: it was sold while both dairy promotions ran. The total after the join, $190, counts L2’s $60 twice; the total at the sales grain is $130. If `rows_after_join` equals `lines_matched` and `lines_repeated` is 0, the join kept the grain and the sum is safe.

The pattern generalises to any join:

*   **Step 1, the grain.** Write one line per table: “`pos_sales`: one row per receipt line. `promotion`: one row per offer.” If you cannot write it, you do not yet know what the join will do.
*   **Step 2, the key.** For the side that should be “one”, `GROUP BY` the columns you join on and look for `HAVING COUNT(*) > 1`. Use the _whole_ join condition, date range included: a promotion is unique by `promotion_id` and not unique by category and day.
*   **Step 3, reconcile.** Count rows and sum the measure in its own table, then after the join. Equal row counts, or a known and wanted multiplication, and an equal total mean ship. Anything else means fix.

## Three fixes that keep the grain

**Filter with `EXISTS` when the right-hand table only decides which rows count.** That is the `total_at_the_sales_grain` column above: the promotion table chooses lines, it adds no measure, so it should never multiply them. On the demo chain’s October this gives the correct $4,077,972.

**Aggregate to the shared grain before joining** when both sides carry a measure. Footfall per store-day and sales per store-day, then join one row to one row:

```
WITH sales AS (
  SELECT store_id, sale_date,
    SUM(net_sales) AS net_sales
  FROM pos_sales
  GROUP BY 1, 2
)
SELECT SUM(t.footfall) AS footfall,
  SUM(s.net_sales) AS net_sales
FROM store_traffic t
JOIN sales s
  ON s.store_id = t.store_id
  AND s.sale_date = t.traffic_date
```

On the demo chain’s October this returns the 247,866 visitors and $10,735,229 of sales that the two tables hold on their own.

**Pick one row on purpose** when the right-hand side has several candidates and the business has a rule: the promotion that gave the deepest discount, the most recent price. Write the rule with `QUALIFY ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...) = 1` and say in the report which one won. A join that picks the first match it finds is a rule nobody chose.

## Write the grain down where joins are made

Every step of the check depends on knowing the grain, and the grain usually lives in somebody’s head. The durable fix is to write it next to the table, once, where every query starts:

*   **Start each table’s description with its grain**, “one row per receipt line”, and make the grain its primary key. The free [Retail Chain data model](/data-models/retail-chain) that the demo data comes from does this for all ten of its tables, and the [grain](/glossary/grain-in-fact-table) glossary page explains the idea.
*   **Declare each relationship once, with its cardinality.** In the model above, `pos_sales` to `store_traffic` is written as many-to-one on store and day. Anyone who reads it knows that summing footfall through that join is wrong before they write it. In OWOX Data Marts the tables are Data Marts and the relationships are declared between them as part of one [data model](/product/data-modeling); [joinable Data Marts](/features/joinable-data-marts) explains how they are used; [OWOX Model Canvas](/blog/articles/owox-model-canvas-vs-drawsql) draws the same model in the browser.
*   **Test the key, not just the query.** A [data quality](/product/data-quality) check on primary-key uniqueness catches a load that duplicated the “one” side, which would fan out every correct join built on it.

The same applies when the query is written by an AI assistant instead of an analyst. An assistant reading raw tables has to guess which columns join and at what grain, and the wrong guess is the same wrong join: in [our write-up of cross-tool MCP joins](/blog/articles/mcp-analytics-limitations-cross-tool-joins), hand-written SQL that joined orders to daily ad spend at the wrong grain returned 30,702.80 where the right total was 47,524.81. Given declared relationships, an assistant reads the grain instead of guessing it. The check above still applies to whatever it writes.

## Before the number ships

A short list to keep beside the query:

*   I can say what one row is in every table in this join.
*   The “one” side is unique on the full join condition (`HAVING COUNT(*) > 1` returns nothing).
*   Rows before and after the join: equal, or multiplied on purpose.
*   The measure’s total in its own table equals the total after the join.
*   No `SUM(DISTINCT)` on money or counts, and no `SELECT DISTINCT` used to make a number look right.
*   Every condition on a `LEFT JOIN`ed table is in `ON`, or is an `IS NULL` test.

More on the two ideas underneath this: [star schema](/blog/articles/star-schema-explained) and [primary and foreign keys](/blog/articles/primary-and-foreign-keys-sql).

## The queries behind the demo figures

All on `owox-demo.retail_chain`, synthetic data, October 2026, run on 2026-11-02.

```
-- Footfall: true, fanned out, and SUM(DISTINCT)
WITH s AS (
  SELECT * FROM `owox-demo.retail_chain.pos_sales`
  WHERE sale_date
    BETWEEN '2026-10-01' AND '2026-10-31'),
t AS (
  SELECT * FROM `owox-demo.retail_chain.store_traffic`
  WHERE traffic_date
    BETWEEN '2026-10-01' AND '2026-10-31')
SELECT
  (SELECT SUM(footfall) FROM t) AS true_footfall,
  (SELECT SUM(t.footfall) FROM s JOIN t
    ON s.store_id = t.store_id
    AND s.sale_date = t.traffic_date) AS joined,
  (SELECT SUM(DISTINCT t.footfall) FROM s JOIN t
    ON s.store_id = t.store_id
    AND s.sale_date = t.traffic_date)
    AS sum_distinct
```

```
-- Promotions: joined against counted once
WITH s AS (
  SELECT s.*, p.category
  FROM `owox-demo.retail_chain.pos_sales` s
  JOIN `owox-demo.retail_chain.product` p
    USING (product_id)
  WHERE sale_date
    BETWEEN '2026-10-01' AND '2026-10-31')
SELECT
  (SELECT SUM(s.net_sales) FROM s
    JOIN `owox-demo.retail_chain.promotion` pr
      ON pr.category_scope = s.category
      AND s.sale_date
        BETWEEN pr.start_date AND pr.end_date)
    AS joined,
  (SELECT SUM(net_sales) FROM s
    WHERE EXISTS (
      SELECT 1
      FROM `owox-demo.retail_chain.promotion` pr
      WHERE pr.category_scope = s.category
        AND s.sale_date
          BETWEEN pr.start_date AND pr.end_date))
    AS counted_once
```

The `LEFT JOIN` figures are the two queries in that section, run on the same October sale lines.

## Topics

[Data Modeling](/blog/topics/data-modeling)

## Related articles

[Data Modeling · What is Apache Ossie? The Open Standard for Semantic Models, Explained · October 19, 2026](/blog/articles/apache-ossie)

[Data Modeling · Benefits of Data Modeling for Business Reporting · November 10, 2026](/blog/articles/benefits-of-data-modeling)

[Data Modeling · Best Data Modeling Tools in 2026 · July 21, 2026](/blog/articles/best-data-modeling-tools)

[See all articles →](/blog/articles)

## Links

Pages this page links to, on this site and on docs.owox.com. Where the page has a Markdown twin, its address follows the link.

- [Data Modeling](https://www.owox.com/blog/topics/data-modeling) — /blog/topics/data-modeling.md
- [Book a Demo](https://www.owox.com/book-a-call) — /book-a-call.md
- [Retail Chain data model](https://www.owox.com/data-models/retail-chain) — /data-models/retail-chain.md
- [grain](https://www.owox.com/glossary/grain-in-fact-table) — /glossary/grain-in-fact-table.md
- [data model](https://www.owox.com/product/data-modeling) — /product/data-modeling.md
- [joinable Data Marts](https://www.owox.com/features/joinable-data-marts) — /features/joinable-data-marts.md
- [OWOX Model Canvas](https://www.owox.com/blog/articles/owox-model-canvas-vs-drawsql) — /blog/articles/owox-model-canvas-vs-drawsql.md
- [data quality](https://www.owox.com/product/data-quality) — /product/data-quality.md
- [our write-up of cross-tool MCP joins](https://www.owox.com/blog/articles/mcp-analytics-limitations-cross-tool-joins) — /blog/articles/mcp-analytics-limitations-cross-tool-joins.md
- [star schema](https://www.owox.com/blog/articles/star-schema-explained) — /blog/articles/star-schema-explained.md
- [primary and foreign keys](https://www.owox.com/blog/articles/primary-and-foreign-keys-sql) — /blog/articles/primary-and-foreign-keys-sql.md
- [Data Modeling · What is Apache Ossie? The Open Standard for Semantic Models, Explained · October 19,…](https://www.owox.com/blog/articles/apache-ossie) — /blog/articles/apache-ossie.md
- [Data Modeling · Benefits of Data Modeling for Business Reporting · November 10, 2026](https://www.owox.com/blog/articles/benefits-of-data-modeling) — /blog/articles/benefits-of-data-modeling.md
- [Data Modeling · Best Data Modeling Tools in 2026 · July 21, 2026](https://www.owox.com/blog/articles/best-data-modeling-tools) — /blog/articles/best-data-modeling-tools.md
- [See all articles →](https://www.owox.com/blog/articles) — /blog/articles.md