Why Your AI's SUM(DISTINCT) Dropped Real Orders
Asked for revenue and got a number that is short by $49. Two different orders were both $49. SUM(DISTINCT amount) keeps each price once, so the second order vanishes.
You ask: "What's revenue from these orders?" The AI says $10,000. Adding the rows yourself gives $10,049. Two orders are $49. Both are real, different order ids, different customers.
SELECT SUM(DISTINCT amount) FROM orders
SUM(DISTINCT amount) builds the set of amounts first. The set contains 49 once. The second $49 order is not a duplicate row. It is a duplicate number, and that is all DISTINCT can see.
Distinct prices are not distinct orders
The function is a reasonable reading of the word "distinct," and a bad reading of "revenue." Revenue is the sum of the rows. A price that sold twice is supposed to be in there twice.
This shows up when the model is trying to undo a join. The join repeated some orders, the total was too high, and SUM(DISTINCT amount) pulled it back down. It also pulled down every legitimate pair of equal prices. The total moves toward the dashboard and then stops short, which feels like rounding. It is missing orders. SELECT DISTINCT on the whole row has the same instinct and the same risk. SUM(DISTINCT ...) is the version that hides it inside one function, so the result is a single number with nothing to inspect.
If the join really did repeat orders, collapse the order, not the price:
SELECT SUM(amount) FROM (
SELECT DISTINCT id, amount FROM orders_joined_to_lines
) one_row_per_order
Better: sum the orders table, and join only when you need a column from the other table. A fan-out is fixed by changing the grain, not by ignoring repeated amounts.
How to catch it
SELECT SUM(amount) AS all_rows,
SUM(DISTINCT amount) AS distinct_prices,
COUNT(*) AS orders,
COUNT(DISTINCT amount) AS different_prices
FROM orders
WHERE <the filter the AI used>
If all_rows and distinct_prices differ, equal amounts were merged. The difference is the revenue of the extra copies. If orders is much larger than different_prices, most prices repeat and the gap can be a large fraction of the total.
The prompt that heads this off
Do not use SUM(DISTINCT amount) for revenue or counts of orders.
Sum every row. If a join has repeated an order, dedupe on the order id
and then sum, and tell me how many rows you removed. Two orders with
the same amount are both real.
The short version
SUM(DISTINCT amount) adds each price once. Two $49 orders become one. Sum the rows, and if you have to remove duplicates, remove them by order id.
Read-only MCP gateway: mcpserver.design.
Related reading: