A successful SQL query can still double-count revenue

A query can run successfully and return the wrong total. Joining orders to their items repeats an order-level amount once for every matching item. Read-only access protects against writes; it does not establish the meaning of a metric.

Google Cloud Tech’s “BigQuery Graph measures explained”, published October 6, prompted this example. We reviewed its description and chapters, not a loaded transcript. That video discusses BigQuery-specific measures. The walkthrough below uses ordinary PostgreSQL SQL; it does not implement Graph measures or imply endorsement.

Define the grain before asking for a total

Our fictional fixture has four orders. Order 101 is $50 with two items; order 102 is also $50 with one item; order 103 is $30 with three items; order 104 is $20 with no items. Amounts are stored in integer cents. The intended metric is the sum of stored order totals: $150 across all four orders. This simplified metric excludes refunds, payment status, tax policy and accounting revenue recognition.

SELECT SUM(o.total_cents)
FROM orders o
JOIN order_items i ON i.order_id = o.id;

This returns 24,000 cents ($240): 50 × 2 + 50 × 1 + 30 × 3. It repeats amounts for orders with multiple items and omits order 104 entirely. Successful execution is not evidence that the answer matches the question.

DISTINCT amounts are not distinct orders

SELECT SUM(DISTINCT o.total_cents)
FROM orders o
JOIN order_items i ON i.order_id = o.id;

This returns 8,000 cents ($80). DISTINCT keeps only the unique amounts 5,000 and 3,000. Orders 101 and 102 are different orders with the same amount; one disappears from the sum. Deduplicate by the entity’s key, not its price.

Choose the population explicitly

For all orders, query the order table directly:

SELECT SUM(total_cents) FROM orders;

Expected result: 15,000 cents ($150). If the question instead means orders with at least one item, use an existence check:

SELECT SUM(o.total_cents)
FROM orders o
WHERE EXISTS (
  SELECT 1 FROM order_items i WHERE i.order_id = o.id
);

Expected result: 13,000 cents ($130). Joining a subquery that selects DISTINCT order_id produces the same result. A LEFT JOIN alone does not fix the grain: it includes order 104 but still repeats the other amounts, returning 26,000 cents ($260). If you need item-level metrics, aggregate those separately to one row per order before combining them with order-level amounts.

What we verified

We created the fixture outside Bufflehead in a disposable PostgreSQL 14 database. Seven checks passed through Bufflehead’s NewPostgresDirect connection and Query methods: the inflated inner join ($240), DISTINCT undercount ($80), all-order total ($150), EXISTS total ($130), distinct-order-ID total ($130), inflated left join ($260), and a session read-only setting of on. The database role was separately granted SELECT access only.

This exercises Bufflehead’s Postgres connection code directly, not the desktop UI, an end-to-end MCP exchange or a live model experiment. The fixture and verification program contain only synthetic data and document reproduction. Database setup, grants and metric definitions remain external to Bufflehead.

Give the AI a checkable question

Try: “Sum stored order totals in cents across all orders, including orders without items. Each order must contribute once. Explain the grain and inspect any join before returning a total.” Then save the actual SQL and compare its result with the known 15,000-cent answer. This is a suggested prompt, not a reported model outcome.

For your own data, include two separate orders with equal amounts, an order with multiple items and an order with none. Define whether an empty population should return NULL or zero, and specify currency and date boundaries. These cases turn a plausible answer into something you can verify.

Try Bufflehead for read-only access to an existing database. Before trusting an AI-generated total, check which entity each row represents and whether each entity contributes the intended number of times.