SQL: Why Money Totals Show Long Decimals

The symptom

You total card payments by type and the answer reads 93756.30999999992. Every amount in the table has two decimal places, and no row holds that number. A reconciliation that checks the total with = 93756.31 finds nothing, though the total is right to the cent.

Why it happens

The amount column is a DOUBLE, a binary floating-point number. It stores whole numbers and halves exactly, but not most cents: 125.42 is kept as the nearest binary fraction, a tiny bit off. Add 1,910 of them and the tiny errors add up to one you can see.

Banking Transactions declares amount DECIMAL(12,2) in its .sql schema file, but the 4,139-row sample that loads in the browser comes from a CSV file, and DuckDB reads its amounts as DOUBLE. Data loaded from CSV, spreadsheets, or JSON often ends up as floating point this way.

The fix

Cast to DECIMAL before you add. A DECIMAL(12, 2) holds each amount as an exact number of cents, so the sum is exact too:

select transaction_type,
       count(*) as transactions,
       sum(amount) as total
from transactions
group by transaction_type
order by transaction_type

select transaction_type,
       count(*) as transactions,
       sum(cast(amount as decimal(12, 2))) as total
from transactions
group by transaction_type
order by transaction_type

The fixed totals end in two decimal places: 93756.31, 22011.38, 7388.37. Better still, store money as DECIMAL in the first place, when you create the table or load the file, so no query has to remember the cast. Lesson 2.7 · Integers, decimals, and floats covers the three number types.

Never compare money with =

The drift breaks equality, not only display. This finds no row, because the DOUBLE total is 93756.30999999992:

select transaction_type, sum(amount) as total
from transactions
group by transaction_type
having sum(amount) = 93756.31

select transaction_type,
       sum(cast(amount as decimal(12, 2))) as total
from transactions
group by transaction_type
having sum(cast(amount as decimal(12, 2))) = 93756.31

The literal 93756.31 isn't the problem: DuckDB reads a number written with a decimal point as a DECIMAL, so 0.1 + 0.2 = 0.3 is true. Make the same sum with DOUBLE values and it is false, because 0.1::double + 0.2::double is 0.30000000000000004. If you have to compare floating-point values, test that they are close, as in abs(total - 93756.31) < 0.005.

round() fixes the display, not the data

round(sum(amount), 2) shows 93756.31, and for a one-off report that is fine. But the result is still a DOUBLE, and any arithmetic you do with it can drift again. Formatting as text, printf('%.2f', sum(amount)) or format('{:.2f}', sum(amount)), gives a tidy string, which then sorts and compares as text. Format in the last step, for the reader, and keep the numbers as DECIMAL until then.

select sum(round(cast(amount as decimal(12, 2)) * 0.015, 2)) as fees_billed,
       round(sum(cast(amount as decimal(12, 2)) * 0.015), 2) as fees_rounded_once
from transactions
where transaction_type = 'card_payment'

Learn more

• Lesson 2.7 · Integers, decimals, and floats
• Reference · CAST and type conversion
• Why integer division makes percentages 0
• Banking Transactions dataset