Ravindra BagaleCourses & study guides Track your progress

6. SQL Data Types Analysts Actually Meet

6.2 Why Money Should Not Be Floating-Point Guesswork

By Last updated:

Why. Banks and shops must get every paisa and every cent right. Decimal numbers stored as REAL can be very slightly wrong.

What. REAL values are stored as binary floating-point numbers, which can only hold an approximation of most decimals like 0.1. A safe habit is to store money as a whole number of cents or paise in an INTEGER column, and divide by 100 only for display. Whole-number sums are exact as long as they stay inside SQLite's integer range, which goes up to about 9.2 quintillion.

How. Compare a REAL sum with an INTEGER cents sum for the same two small deposits.

Steps

  1. Run the first example and look at the result of 0.1 + 0.2 = 0.3.
  2. Run the second example and compare the two sum columns.
  3. Notice that amount_cents gives an exact whole number.
  4. Divide the cents total by 100.0 only when you show the final money value.

Examples

Example 1 · dataset: northstar_bank

SELECT 0.1 + 0.2 = 0.3 AS looks_equal;

Output

looks_equal
0

Meaning. The answer is 0, which means false. In floating point, 0.1 plus 0.2 is not exactly 0.3.

Example 2 · dataset: northstar_bank

SELECT SUM(amount)               AS real_sum,
       SUM(amount_cents)         AS cents_sum,
       SUM(amount_cents) / 100.0 AS cents_as_money
FROM transactions
WHERE txn_id IN (22, 23);

Output

real_sumcents_sumcents_as_money
0.30000000000000004300.3

Meaning. The REAL sum carries a tiny error at the end. The cents sum is exactly 30, which is 0.30 when you divide by 100.

Ravindra Bagale's Tip

Some apps round the display, so a REAL sum may look correct on screen. Check money totals with an INTEGER cents column when one exists.