Back to Blog

Why Your AI's Combined Revenue Includes the Refunds

Asked for shop revenue plus app revenue and got $10,900. The app query lists refunds first. UNION ALL stacks by position, so the app's refunds landed in the revenue column. No error, both columns are numbers.

You ask: "Shop revenue plus app revenue." The AI says $10,900. The shop did $10,000, with $400 of refunds. The app did $14,000, with $900 of refunds. Real revenue is $24,000.

SELECT revenue, refunds, 'shop' AS source FROM shop_monthly
UNION ALL
SELECT refunds, revenue, 'app'  AS source FROM app_monthly

UNION ALL does not look at names. It lines up the first column with the first column. For the app, the first column is refunds. Those $900 land under revenue, and the $14,000 lands under refunds.

The stacked revenue column is 10,000 + 900 = $10,900. Every figure in it is a real number from a table. Half of them are refunds.

Position, not the name

The first query decides the headings. Anything after UNION ALL is data in that same order. An alias on the second query changes nothing about where the value goes. SELECT refunds AS revenue is still in the first position, so it still fills the revenue column.

SELECT revenue, refunds, 'shop' AS source FROM shop_monthly
UNION ALL
SELECT revenue, refunds, 'app'  AS source FROM app_monthly

Same three columns, same order, on both sides. The source label is what lets you check each side instead of trusting the blend.

This is not UNION dropping a person who appears in both lists. There, a whole row disappears. Here every row survives and the numbers change seats. Because both columns are numeric, nothing fails. A text column in that position would have errored, and the error would have been the safer outcome.

How to catch it

Sum each source on its own, then sum the stacked query.

SELECT
  (SELECT SUM(revenue) FROM shop_monthly)
  + (SELECT SUM(revenue) FROM app_monthly) AS real_revenue

Compare that to SUM(revenue) of the union. If they differ, a column is in a different position. Do the same for refunds. The "missing" revenue is usually sitting in the other column, and the two gaps match.

The prompt that heads this off

When you UNION queries, select the same expressions in the same order
on every side, and add a source column. Do not rely on aliases to line
columns up. After you stack them, sum each source separately and show
me those sums next to the combined total.

The short version

UNION ALL stacks by position. The second query's column names do not matter. Put revenue in the same slot on every side, label the source, and check each side's sum against the blend.


Read-only MCP gateway: mcpserver.design.

Related reading: