Back to Blog

Why Your AI's Total Is Right on Every Row, and Wrong When You Add Them

The AI listed 500 orders and a revenue column of $10,000 on every row. That $10,000 is already the total. Adding the column makes $5 million. The query never touched an order twice.

You ask: "List recent orders, and include the revenue." The AI comes back with 500 rows. The revenue column reads $10,000 on every one of them. You add the column. $5,000,000. Finance has $10,000. No order was duplicated.

SELECT id, SUM(amount) OVER () AS revenue
FROM orders

SUM(amount) OVER () computes the total once, for the whole result, and stamps that total onto every row. Each row is telling you the same fact. Adding the column counts that fact once per row.

A total copied is not an amount

The order's own amount is amount. The figure that is identical on every row is the grand total. They get mixed up because both are money and both sit in a column.

-- one total, one row
SELECT SUM(amount) AS revenue FROM orders

-- the rows, plus the total labeled as a total
SELECT id,
       amount,
       SUM(amount) OVER () AS total_for_all_rows
FROM orders

Sum amount. Do not sum total_for_all_rows.

A running total is the variant that does not look flat:

SELECT id, SUM(amount) OVER (ORDER BY created_at) AS revenue
FROM orders

Row 1 shows the first order. Row 2 shows the first two. The last row shows the real total. Add that column and you add every partial total, which is far more than the revenue and not a tidy multiple of it. The last value was the only one worth reading.

This is not a join repeating orders. Count the ids. If each id appears once, the rows are fine and the column you added is a copy of a total. It is also not SUM returning NULL. You got a number. You got it too many times.

How to catch it

Wrap the AI's result and look at whether revenue varies.

SELECT COUNT(*) AS rows,
       COUNT(DISTINCT revenue) AS distinct_totals,
       SUM(revenue) AS summed_column,
       MAX(revenue) AS one_total
FROM ( <the AI's query> ) q

If distinct_totals is 1 and summed_column equals rows * one_total, every row carries the grand total. The revenue is one_total.

If the values climb and the last one equals SELECT SUM(amount) FROM orders with the same filter, you have a running total. The revenue is that last value, not the sum of the column.

The prompt that heads this off

When I ask for rows and a total, select the row's own amount and a separate
grand total. If you use SUM() OVER (), name the column total_for_all_rows
and do not sum it. If you use a running total, say that only the last row
is the overall total.

The short version

SUM(amount) OVER () prints the whole total on every row. Adding that column multiplies the revenue by the number of rows. Sum the order amount. Read the repeated total once.


Read-only MCP gateway: mcpserver.design.

Related reading: