Is a few cents always a FLOAT column?

It's the first thing to check. Other bugs (JOIN fan-out, integer division, a missing day in BETWEEN) move the number by a lot. FLOAT drift is usually cents, or a total that won't match a hand-added sample of the same rows.

Does Postgres REAL/DOUBLE PRECISION have the same issue?

Yes. Those are binary floating point. NUMERIC and DECIMAL store exact base-10 amounts. Money-like columns should be NUMERIC(12,2) or similar, not FLOAT.

Can the AI just ROUND() the SUM and call it fixed?

ROUND hides the display. It does not make 0.1 + 0.2 equal 0.3 in binary float. If you reconcile against Stripe or a ledger, round-at-the-end still drifts as the row count grows.

What if I don't control the schema?

Cast at query time: SUM(amount::numeric) in Postgres, CAST(amount AS DECIMAL(12,2)) in MySQL/SQL Server. Still not as safe as storing NUMERIC, but it stops the running total from accumulating binary noise.

Is this the same as integer division returning 0%?

No. Integer division loses the entire fraction. FLOAT keeps a fraction — just not the exact cents you think you stored.

FLOAT Money Rounding SQL | Numeric vs Float

You ask for last month's revenue. The AI returns $40,218.17. Finance has $40,218.00. You pull ten rows and add them by hand — they add up. The SQL has no extra join. The column is DOUBLE (or FLOAT / REAL), and those types cannot store 0.10 exactly.

Binary fractions vs cents

0.1 in binary floating point is a repeating approximation, the same reason 0.1 + 0.2 === 0.3 is false in JavaScript. A single row looks fine when you print two decimal places. SUM over tens of thousands of rows surfaces the leftover bits as a few cents (sometimes more).

-- looks like money, isn't exact
SELECT SUM(amount) FROM payments;   -- amount is DOUBLE

-- exact cents
SELECT SUM(amount) FROM payments;   -- amount is NUMERIC(12,2)

Same query text. Different type, different total. No error either way.

This is a small-error cousin of JOIN fan-out (totals that are multiples too high) and integer division (rates that collapse to 0). FLOAT drift is the one that looks almost right — which is why it survives a casual glance.

How to tell what you have

describe_table on the payments/orders table. If amount, price, total, or refund is real, float, float8, double, or double precision, you are in this bug. numeric, decimal, and money (Postgres) are the types that keep cents.

MySQL's FLOAT/DOUBLE vs DECIMAL is the same split. SQL Server: FLOAT/REAL vs DECIMAL/NUMERIC.

What to do in the query (when you can't change the column)

Cast before you add:

-- Postgres
SELECT SUM(amount::numeric(12,2)) FROM payments;

-- MySQL / SQL Server
SELECT SUM(CAST(amount AS DECIMAL(12,2))) FROM payments;

Still not a substitute for storing money as decimal. It does stop the SUM from adding binary dust to binary dust. If you need to reconcile to the cent, say so in the prompt and ask for the raw type first.

How to catch it

Ask describe_table for the money column's type
Re-run SUM with an explicit decimal cast
Compare both totals to a number you already trust (Stripe, the admin UI, last month's invoice)

A 2-cent gap that disappears after the cast is this. A 3× gap is still a join.

The prompt that heads this off

Before summing amount/price/total/refund, describe_table and check
the type. If it's FLOAT/REAL/DOUBLE, cast to NUMERIC/DECIMAL before
SUM and say so. Don't treat a 2-decimal printout as exact cents.

The short version

Two decimal places on the screen are not two decimal places in the column. If revenue is a few cents off and the SQL looks clean, read the type before you rewrite the join.

Read-only MCP gateway: mcpserver.design.

Related reading: