What if 95% of orders are in one currency?

The total is still wrong, and now it looks right. A handful of large orders in another currency can shift it by a few percent without anyone noticing. Group by currency first and see how many there are.

What if there is no currency column?

Then the table either assumes one currency or stores a converted value in another column such as amount_usd. Ask which column is the reporting currency before summing. Do not let the AI guess.

Can the AI just convert the amounts?

Only if the database holds exchange rates. A rate the model remembers is invented and has no date. Use a rates table, name the date or the rule (rate on the order date, or one fixed rate), and have the AI say which.

Is this the same as the cents problem?

No. Cents versus dollars is a scale problem inside one currency. This is several currencies sharing one column. They overlap on zero-decimal currencies such as JPY, where Stripe stores 500 for 500 yen, so dividing everything by 100 is also wrong.

Is it fine to just leave other currencies out?

Yes, if the answer says so: 'USD only, excludes 412 orders in other currencies.' The mistake is leaving them in silently or dropping them silently.

SUM Across Currencies | Mixed Currency Total

You ask: "What's our total revenue?" The AI answers 70,000. Finance has a different figure. The query is fine. The column is the problem.

SELECT SUM(amount) FROM orders

The orders table has a currency column. Six hundred orders are in USD, four hundred in EUR. SUM(amount) adds them all. A hundred dollars plus a hundred euros is "200" of nothing.

One column does not mean one unit

-- what you need to see first
SELECT currency, COUNT(*) AS orders, SUM(amount) AS total
FROM orders
GROUP BY currency

If that returns more than one row, a plain SUM(amount) mixes units. The result has no currency label, so it reads as a normal number. That is why nothing flags it.

This is not rounding on floats, which moves a total by cents. Here the total can be off by thousands, and every individual row is correct.

Stripe and many payment tables store amounts in the smallest unit, so 1999 means 19.99 USD. Zero-decimal currencies like JPY store the whole amount, so 500 means 500 yen. Dividing every row by 100 is right for most currencies and wrong for those.

How to fix it

-- one currency, said out loud
SELECT SUM(amount) / 100.0 FROM orders WHERE currency = 'usd'

-- every currency, side by side
SELECT currency, SUM(amount) / 100.0 FROM orders GROUP BY currency

-- converted, only if you have a rates table
SELECT SUM(o.amount / 100.0 * r.rate_to_usd)
FROM orders o
JOIN fx_rates r ON r.currency = o.currency AND r.rate_date = o.created_at::date

The third form needs a real rates table. Without one, "converted" means the model made up a rate. Name the date or rule you used (rate on the order date, or one fixed rate), because a different rule gives a different number.

How to catch it

SELECT COUNT(DISTINCT currency) FROM orders WHERE <the filter the AI used>
-- anything above 1 means a bare SUM mixes currencies

Do the same check before any AVG or MAX on an amount column. They mix units the same way, as in averages hiding which rows they cover.

The prompt that heads this off

Before summing or averaging an amount column, check whether the table has
more than one currency. If it does, group by currency or convert using a
rates table, and tell me the currency (and date or rate) of every total.
Never add amounts in different currencies.

The short version

SUM(amount) adds numbers, not money. If the table holds more than one currency, group by currency first, then convert with a dated rate or report a single currency and say so.

Read-only MCP gateway: mcpserver.design.

Related reading: