1. LandOffer
  2. Blog
  3. All articles
  4. A SQL Join Doubled Your Revenue: Diagnose Fanout Before Shipping the Report

Data & Decisions

A SQL Join Doubled Your Revenue: Diagnose Fanout Before Shipping the Report

Find where a join changes the grain of an order-level amount. Actual isolated SQLite examples show exact doubling, equal-valued distinct orders, unmatched rows and varying child counts, followed by a row-level reconciliation.

10 min readLandOffer team

When a join doubles a report's revenue, check the grain of the amount and the rows created by the join before changing the aggregation. A one-to-many join can repeat an order-level amount once per matching item. Reconcile identifiers, match counts, unmatched rows and totals. Repair the relationship or aggregate at the required grain; do not hide the problem with SUM(DISTINCT amount) or an unexplained divisor.

This LandOffer.ai guide includes original synthetic tables and SQL actually executed in disposable in-memory SQLite 3.54.1. The report's illustrative metric is gross order-header amount in one currency, stored as integer cents. It is not validated accounting revenue or guidance on recognition, tax or refunds. No business data, warehouse, BI account or published report was accessed or changed. Primary documentation was checked October 27, 2026.

Market workers compare a whole order basket with its component bundles before counting the same large weight repeatedly.

Write the measure's grain and population first

Ask what one source row represents and where the amount belongs. An order header, item, shipment and payment attempt are different grains. A total valid on the order table may become repeated after joining items, even when both source tables contain perfectly legitimate records.

Define which orders belong in the report. Include currency, period, relevant status and treatment of missing values under the actual metric owner's rules. A query can fix fanout while still selecting the wrong population. The fixture below deliberately has one currency, no dates, no status exclusions and non-null integer amounts so the join problem can be inspected separately.

PostgreSQL 18's table-expression documentation explains that an inner join creates a result row for each matching pair. A left join additionally preserves unmatched left rows with nulls on the right. Those rules explain the mechanism; they do not establish that a particular business relationship or report definition is correct.

Save the selected header identifiers and their baseline amount before adding the join. Compare the order count and amount against the joined result, then inspect affected identifiers. An equal total alone is not enough: an omitted order and an overcounted order can offset each other. Keep the contributing rows available for review.

Worked asset: a minimal join that doubles the amount

The first executed fixture contains these three rows in orders. The primary key makes order_id unique. Dollars below are display conversions of exact integer-cent inputs, not floating-point calculations or real sales.

order_id amount_cents Display amount
101 10000 $100.00
102 10000 $100.00
103 20000 $200.00

The separate items table has six rows. Each item has its own primary-key identifier, and two items reference each order:

item_id order_id
1 101
2 101
3 102
4 102
5 103
6 103

The correct baseline for this stipulated order-header metric is 10000 + 10000 + 20000 = 40000 cents, or $400.00. The item rows do not contain independent copies of the order's revenue. They describe the one-to-many relationship.

The following query was actually run:

SELECT SUM(o.amount_cents)
FROM orders o
JOIN items i ON i.order_id = o.order_id;

It returns 80000 cents, or $800.00. The completed joined-row inspection explains why:

Order Matching item Header amount appearing on this joined row
101 1 10000 cents
101 2 10000 cents
102 3 10000 cents
102 4 10000 cents
103 5 20000 cents
103 6 20000 cents

There are six joined rows but only three distinct order identifiers. The sum counts order 101 twice, order 102 twice and order 103 twice. No source-order duplicate is required to create that repetition. The sum is now answering a different question: add the whole order amount for every item match. That is why a valid relationship can still produce the wrong report measure.

The fixture independently checks that no duplicate order key exists. That does not make items.order_id unique: its repeated values are intentional. Check the key on the side expected to contribute at most one row for the desired result, rather than declaring every repeated foreign key bad data.

Why distinct amounts and dividing by two fail

Running SUM(DISTINCT o.amount_cents) on this joined fixture returns 30000 cents, or $300.00. It removes the repeated amount 10000 as a value, even though orders 101 and 102 are different valid orders that both contribute $100.00. Amount equality cannot establish order identity.

SQLite's aggregate documentation states that DISTINCT filters duplicate input values before aggregation. That is useful when distinct values are actually the question. It is not a general way to deduplicate business facts. Selecting distinct order identifiers with their correct header values can serve a different, deliberate purpose; including item-level columns in that selection may preserve multiple rows per order again.

Dividing 80000 by two happens to match the first baseline because every order has exactly two items. It is not a relationship repair. Later in the executed fixture, adding a third item to order 103 changes the erroneous joined sum to 100000 cents. Dividing by two gives 50000 cents while the applicable four-order baseline is 45000. A constant adjustment conceals the dependency on match counts.

Google Cloud's Looker symmetric-aggregates guide, last updated July 22, 2025, illustrates repeated order totals after a one-to-many join and emphasizes a unique primary key and correct modeled relationship. That is a product-specific aggregation mechanism, not proof that every BI model or arbitrary SQL query automatically handles fanout. No Looker account or generated query was tested here.

Inspect matches before deciding what to remove

A useful diagnostic groups the raw join by the fact identifier rather than hiding it inside the final sum. The local script also executes this inspection:

SELECT o.order_id, COUNT(*) AS joined_rows,
       COUNT(i.item_id) AS matching_items
FROM orders o
LEFT JOIN items i ON i.order_id = o.order_id
GROUP BY o.order_id
ORDER BY o.order_id;

In the final extended fixture, order 104 has one joined row but zero matching items. The left join creates its null-extended row, so COUNT(*) alone would incorrectly suggest one item if labeled as an item count. COUNT(i.item_id) counts the non-null child identifier instead. Orders 101, 102 and 103 have two, two and three matching items respectively. This diagnostic and the corrected child summary agree on those counts.

Check filters at the same time. A left join does not preserve the intended population if a later condition removes its unmatched rows. The script applies a positive-item-count filter to the corrected output and observes 40000 cents instead of 45000: order 104 is excluded. That could answer a deliberately different question about orders with items. It does not satisfy this report's stated all-orders population. Do not add the condition just to make an unfamiliar zero-count row disappear.

An unexpected unmatched order is a reason to investigate the population, not a reason to discard it so the report looks tidier. Once the matching rule, inclusion policy and measure grain are clear, the appropriate correction is easier to review.

Repair the query according to what the report needs

If the question is only the total order-header amount, summing the properly selected orders table can be sufficient; joining items adds no needed information. This example's second report also needs an item count per order, including orders that currently have no items. That calls for a child summary with at most one row per order, then a left join to the header population.

The executed correction is:

WITH item_counts AS (
 SELECT order_id, COUNT(*) AS item_count
 FROM items
 GROUP BY order_id
)
SELECT o.order_id, o.amount_cents,
       COALESCE(i.item_count, 0) AS item_count
FROM orders o
LEFT JOIN item_counts i ON i.order_id = o.order_id
ORDER BY o.order_id;

item_counts has one row per item-side order identifier. The join can therefore retain one output row for each unique header order. The count's zero means no matching item group in this fixture. It does not turn an unknown header amount into zero; the header amount is non-null by the example's declared schema.

This is not a universal prescription to preaggregate every table. If the report requires item-category revenue, adding category to this summary changes the grain and can repeat the header again. Allocating an order amount across categories needs an agreed attribution rule or an appropriate item-level measure. A grouping column does not supply that rule.

A careful comparison keeps one whole-order weight per basket while component counts remain a separate measure.

Worked reconciliation: preserve unmatched orders and varying match counts

The second completed asset extends the same in-memory fixture in two recorded steps, preserving the result of each step. First it adds order 104 with 5000 cents and no item rows. Under the defined report population, that order must remain included. The baseline becomes 45000 cents, or $450.00. The original inner join still returns 80000 cents because it drops the unmatched order while repeating the other three.

The corrected query returns the following four rows. The last column is the amount included once in the reconciled total, not an item allocation:

Order Header amount Matched item count Amount included once
101 10000 cents 2 10000 cents
102 10000 cents 2 10000 cents
103 20000 cents 2 20000 cents
104 5000 cents 0 5000 cents
Total 45000 cents 6 items 45000 cents

The script groups the header keys and the aggregated child keys separately, looking for counts greater than one. Both duplicate-key checks return no rows in this fixture. It identifies order 104 as unmatched instead of silently discarding it. Summing the corrected output in SQL and independently adding its amounts in Python both yield 45000 cents. Agreement here supports this fixture, not a production financial reconciliation.

Next, the script inserts item 7 referencing order 103. The header table and its 45000-cent baseline do not change. The corrected output's item counts become 2, 2, 3, 0; its four header amounts and total remain unchanged. The bad join reaches 100000 cents, and the divide-by-two workaround produces the wrong 50000-cent result.

These two extensions test different risks. The unmatched order checks the chosen inclusion policy and join type. The extra child checks whether the amount remains at order grain when match counts differ. Neither extension proves behavior for refunds, multiple currencies, duplicate source keys, slowly changing dimensions or another joined child table.

The local acceptance record reads: “For the synthetic single-currency order-header measure, the corrected query preserves all four order identifiers, returns one row each, retains the unmatched order with zero matching items and reconciles to 45000 cents. Changing order 103's child count changes item count, not the header total. Production metric approval and actual-source validation remain pending.” The corrected fixture is ready for review within its stated boundary. It does not approve a production report, and no business total or published result was changed.

Review every added relationship before shipping the report

Carry the grain statement through each join, filter and grouping. If a second child table is introduced, two independent one-to-many relationships can multiply one another. Recheck intermediate row counts and identifiers at that step instead of trusting the result because the first join was repaired.

Inspect unexpected key multiplicity as evidence, not an excuse to choose an arbitrary surviving row. A dimension with several versions may require a valid time or status condition. A duplicated business key may need upstream correction. MAX, MIN or a random row can produce a stable-looking number while selecting the wrong fact.

Use permitted representative cases and reconcile with the report owner before publishing. Check equal-valued distinct facts, unmatched rows, variable child counts and the actual inclusion policy. Keep null handling and currency or period rules explicit where they matter. The fixture does not test those broader rules, access controls, query performance or warehouse-specific execution.

When the number changes after a join, stop at the first transformation where the expected grain changes. Explain which identifiers multiplied, why the repaired query counts the measure once and which other definitions remain unverified. LandOffer.ai publishes related data guidance; the next step is a reviewed, row-level reconciliation rather than a reassuring unexplained total.

Sources and Further Reading