Why AI-Generated SQL Counts the Same Sale More Than Once

Two orders worth 100 each become a sales total of 800. The AI assistant has not invented a transaction, and the database has not made an arithmetic mistake. The generated query joined orders to their items and shipments, then added an order-level amount once for every matching combination. Everything executed successfully.

This is an illustrative reporting example, not a customer incident or a model benchmark. It poses a practical question for analytics engineers and managers using conversational data tools: when does a working query justify trusting its number? Before accepting the answer, establish what one row represents, what each join does to that row, and which entity owns the measure.

Name the thing being counted before asking for SQL

“Sales this month” is not a complete metric definition. It might mean booked order value, shipped item value, cash collected, or accounting revenue. Those quantities have different populations, dates and exclusions. A model cannot settle the choice by finding a conveniently named amount column.

Start with a plain sentence: “Sum the booked value of eligible orders placed in this reporting period, counting each order once.” Then identify the source key, status rules, date field, time zone, currency and treatment of cancellations. Call this metric booked order value, not statutory revenue. Accounting recognition requires its own approved definition.

The grain is the meaning of one row. An orders table might contain one row per order; items, one per order line; shipments, one per dispatch. Microsoft's star-schema guidance distinguishes facts from dimensions and emphasizes consistent fact-table grain. The essential lesson here is to write the grain explicitly, not assume it from a table name.

For an AI tool, expose this definition alongside the schema. Column descriptions alone are insufficient: the tool also needs allowed join keys and multiplicities, approved measures, and dimensions across which each measure can be summarized. Our data-quality guide explains why fresh, complete data can still answer the wrong business question.

Follow one order through the multiplication

A join produces combinations, not a promise to preserve business entities. PostgreSQL's table-expression documentation specifies that an inner join emits a row for each matching right-hand row. A left join retains unmatched left-hand rows, but does not stop matched rows from multiplying.

Consider this deliberately small fixture. Both orders belong to the same reporting period and currency. Items sum to each order's value; there are no discounts, taxes, cancellations or refunds in this example.

For A, joining both child tables on order ID pairs every item with every shipment: two times three gives six rows. B produces two. The query below therefore measures the sum of repeated order values, not the sum of orders:

SELECT SUM(o.order_total) AS booked_value
FROM orders o
JOIN order_items i ON i.order_id = o.order_id
JOIN shipments s ON s.order_id = o.order_id;

This expansion is often called fanout. It is not automatically a database defect: one order genuinely can have several items and shipments. The defect is adding a measure at a finer, unintended grain. A foreign key can be valid while the aggregate is wrong. Even summing item amounts here fails: each item is repeated for its order's shipments.

DISTINCT on the amount is not a repair

Ask an assistant to “remove duplicates,” and it may propose SUM(DISTINCT o.order_total). On this fixture that returns 100, because the two legitimate orders happen to have equal amounts. The original query overcounts; the quick repair undercounts.

The identity being protected is the order key, not the numeric value. COUNT(DISTINCT order_id) can answer a distinct-order count, but it does not repair unrelated sums. Applying SELECT DISTINCT after an aggregate cannot undo the rows that already contributed to it. Arbitrarily keeping the first child row may discard information needed elsewhere in the report.

Google's explanation of Looker symmetric aggregates describes a different approach: aggregate measures with their entity keys so repeated join rows do not repeatedly contribute the same entity's value. It depends on a genuinely unique primary key and correctly specified join relationships, and can add computational cost. This is not equivalent to deduplicating monetary values, nor a guarantee that every semantic model is configured correctly.

Before accepting any deduplication, ask: which real entity is duplicated, which key proves that identity, and why should the remaining fields be identical? If those answers are missing, a smaller result is not evidence of a better query.

Aggregate independent facts separately

If the question needs only order value, query orders alone. Every additional join should have a stated purpose. If a child table only determines eligibility, an existence test can express “has at least one qualifying child” without bringing all its rows into the aggregate.

If a report needs order value, line value and shipment count together, summarize each child to one row per order before joining. For the fixture, a possible shape is:

WITH item_totals AS (
  SELECT order_id, SUM(line_total) AS item_total
  FROM order_items
  GROUP BY order_id
), shipment_counts AS (
  SELECT order_id, COUNT(*) AS shipment_count
  FROM shipments
  GROUP BY order_id
)
SELECT
  o.order_id,
  o.order_total,
  i.item_total,
  COALESCE(s.shipment_count, 0) AS shipment_count
FROM orders o
LEFT JOIN item_totals i ON i.order_id = o.order_id
LEFT JOIN shipment_counts s ON s.order_id = o.order_id;

The output is one row per order if orders has a unique order key. It returns A with 100, 100 and three shipments, and B with 100, 100 and two shipments. Summing the order column gives 200; summing shipment counts gives five. Null item totals remain visible instead of pretending that missing detail has a value of zero.

This is a query pattern, not a drop-in production report. Validate key uniqueness, item and shipment definitions, permissions, population filters and snapshot consistency in the actual warehouse. A join added later can reintroduce fanout. Inspect the final output grain, not just the intermediate common table expressions.

Preserve the population when adding filters

Suppose the requested measure is value of all eligible orders, plus how many shipments have been delivered. Putting s.status = 'delivered' in a final WHERE clause after a left join removes orders without a matching delivered row. Filtering inside the shipment summary preserves the orders population; filtering the orders population deliberately answers a different question. The distinction must be specified before generating SQL.

Nulls create another trap. PostgreSQL documents that COUNT(*) counts rows, while COUNT(expression) counts non-null inputs; most aggregates, including SUM, return null for an empty input. See its aggregate-function reference. After a left join, an unmatched order still has a result row, so counting all rows is not the same as counting shipments.

Zero is appropriate for the absence of shipments only when the feed is complete and absence really means none. A missing source partition or delayed ingestion is not evidence of zero activity. Preserve an explicit completeness check outside the numeric result.

Dates and currencies need equally clear boundaries. “Orders placed in September” differs from “shipments delivered in September.” Do not mix currencies or change rounding rules while fixing a join; see the quantity and calculation guide. Use the same data snapshot for comparisons. A corrected order read later can otherwise look like a query discrepancy; the temporal-data guide explains that distinction.

Some breakdowns require a business rule, not different syntax

A customer may belong to two campaigns. An order may contain several product categories. Correctly counting an order once in each category still means category totals cannot necessarily be added to obtain the overall order total.

Choose what the breakdown means. “Value of orders containing this category” permits overlapping groups and must be labeled non-additive. “Value of items in this category” uses line-level facts. “Order value allocated to categories” needs a documented allocation rule, including how discounts or shipping are distributed. Equal division is a policy choice, not something a model should silently invent.

Historical dimensions create a related risk. A customer table with several versions per customer is not unique on customer ID alone. Joining every version can multiply orders; joining only the current version may reclassify historical sales. Decide whether the report needs attributes at order time or today's attributes, then test the effective-date lookup and non-overlapping validity intervals.

ZharfAI's recommendation is to withhold an additive total when the requested dimension has no defensible attribution rule. Showing a clear question to the metric owner is more useful than producing a polished chart whose parts cannot be reconciled.

Give the assistant a metric vocabulary and a bounded execution path

Offer approved metric definitions before offering a warehouse full of tables. Each definition should name its entity key, source grain, filters, time basis, permitted dimensions and non-additive cases. The assistant can map a reader's phrasing to a candidate definition and ask about ambiguity; it should not silently create a new definition under an existing metric name.

A practical review path has three outputs: the selected definition, the proposed query, and a compact evidence record. Record the schema or semantic-model version, parameters, snapshot, source and result counts, key checks, and reconciliation result. Keep sensitive rows out of chat transcripts; use approved views and bounded read-only execution, with access and resource limits enforced independently of generated text.

These controls solve different problems. Read-only access limits mutations; it does not prevent unauthorized disclosure or an expensive query. Successful parsing proves syntax, not population or arithmetic meaning. A semantic layer reduces opportunities for improvised joins, but remains software whose keys and definitions require tests. A second model agreeing with the first is not independent evidence of correctness.

Test the query on data designed to expose its mistake

Production-data checks and small logic fixtures answer different questions. dbt's data-test documentation separates uniqueness, non-nullness and relationship tests. A relationship test that finds every child key in a parent table does not establish that the parent contains only one row per key. Check both sides of the claimed relationship.

dbt's unit-test documentation describes testing SQL logic with small static inputs before materializing a full model. The same testing idea can be used without dbt: construct expected answers independently, then execute the candidate against controlled fixtures in the target dialect.

For this use case, include equal-priced distinct orders, several children on both sides, an order with no shipments, null keys, an orphan child, duplicate parent keys, refunds, and a historical dimension with overlapping intervals. The expected result should distinguish “reject invalid data” from “return zero.” Do not let the same generated query calculate its own answer key.

Add change-based tests. Adding a shipment must not alter booked order value. Splitting one item into two lines with the same combined amount must not alter order value. Adding an eligible order must increase the total by that order's amount, even when another order has the same amount. These tests directly challenge the identity and grain assumptions.

Reconcile at more than the grand-total level: compare per-order contributions and deliberately difficult segments against a separately approved calculation. Two errors can cancel in the total. A sample containing only one item and one shipment per order will never expose the multiplication illustrated here.

Decide whether to accept, rewrite or withhold the number

Use evidence to choose a narrow outcome:

After adoption, monitor uniqueness failures, multiplicity changes, unmatched keys, source completeness and reconciliation differences by segment. Revisit the query when a dimension gains history, a new shipment type arrives, a model changes its preferred join path, or the metric owner revises the definition.

The useful acceptance claim is not “the AI wrote valid SQL.” It is: “this result counts the intended entities under a named definition, and the joins have been tested against cases that would otherwise change the answer.”

Source notes — reviewed September 16, 2026

This guide combines documented database behavior with ZharfAI's proposed review method. The 200-to-800 example is a constructed arithmetic fixture, not measured model performance or accounting advice.

• PostgreSQL 18 table expressions — version-pinned join, filtering and grouping semantics; reviewed on the date above.
• PostgreSQL 18 aggregate functions — counting, nulls and empty-input behavior; reviewed on the date above.
• Google Cloud Looker symmetric aggregates — updated July 22, 2025; key-aware aggregation and modeling prerequisites.
• Microsoft Power BI star-schema guidance — updated December 30, 2024; fact grain, relationships and historical dimensions.
• dbt data tests — updated September 11, 2026; distinct assertions about existing data.
• dbt unit tests — updated September 14, 2026; static-input SQL logic tests, available from dbt 1.8 subject to documented model and adapter constraints. Rolling documentation is not a claim that every installed version supports every feature.