SQLite Basics: Avoid Surprises in Integer and REAL Arithmetic

A percentage query can return zero even when work is partly complete. In SQLite, division between two integer values produces an integer result, while a REAL operand selects floating-point arithmetic. The actual values matter, not just the type name in a table declaration.

These examples use core SQLite operators and functions, with no optional math or decimal extension. All shown results were tested on SQLite 3.53.1. The expression reference documents arithmetic and integer division, which truncates toward zero.

Prepare a scratch directory

Tested on Linux with Python 3.12.14 and SQLite 3.53.1. You need Bash, Python 3 with SQLite support, a text editor, and mktemp, rm, and rmdir. No additional Python packages are needed. Keep the same terminal open and save files as UTF-8.

start_dir="$PWD"
work_dir="$(mktemp -d)" || exit 1
cd -- "$work_dir" || exit 1

The standalone SQLite CLI was unavailable on the test machine. Save this runner as run-sql.py. It prints pipe-separated rows and displays missing values as NULL. It does not escape embedded pipes or newlines, so it is a teaching runner, not a general SQL shell or CSV exporter. Put each statement’s ending semicolon at the end of a line, as shown; do not put two complete statements on one line.

from pathlib import Path
import sqlite3
import sys

connection = sqlite3.connect(":memory:", isolation_level=None)
try:
    statement = ""
    for line in Path(sys.argv[1]).read_text(encoding="utf-8").splitlines(True):
        statement += line
        if sqlite3.complete_statement(statement):
            cursor = connection.execute(statement)
            for row in cursor:
                print("|".join("NULL" if value is None else str(value) for value in row))
            statement = ""
    if statement.strip():
        raise ValueError("Unfinished SQL statement")
finally:
    connection.close()

Each invocation starts with a fresh in-memory database. The examples are independent, and the toy data disappears when the connection closes.

1. Promote a value before dividing

Save as calculate-completion-ratios.sql:

CREATE TABLE progress (
    name TEXT PRIMARY KEY,
    completed INTEGER NOT NULL,
    total INTEGER NOT NULL,
    CHECK (total >= 0 AND completed BETWEEN 0 AND total)
);
INSERT INTO progress VALUES
    ('Drafts', 3, 4),
    ('Empty', 0, 0),
    ('Reviews', 1, 2);
SELECT name,
       completed / total AS integer_ratio,
       CAST(completed AS REAL) / NULLIF(total, 0) AS real_ratio,
       100.0 * completed / NULLIF(total, 0) AS percent_complete
FROM progress
ORDER BY name;

Run:

python3 run-sql.py calculate-completion-ratios.sql

Output:

Drafts|0|0.75|75.0
Empty|NULL|NULL|NULL
Reviews|0|0.5|50.0

The integer ratios discard the fractional part. Casting completed before division preserves the fraction, as does multiplying by 100.0 before dividing for a percentage. Casting the already-truncated result would be too late.

An empty workload has no defined completion ratio here. NULLIF makes the zero-denominator policy explicit; SQLite already returns NULL for division by zero rather than raising an exception. Do not automatically convert that result to zero unless your report genuinely means that an empty workload is zero percent complete.

2. Do not assume NUMERIC means floating point

Save as inspect-numeric-affinity.sql:

CREATE TABLE measurements (
    label TEXT PRIMARY KEY,
    value NUMERIC NOT NULL
);
INSERT INTO measurements VALUES ('A', 21.0), ('B', 24.5);
SELECT label, value, typeof(value),
       value / 28 AS original_ratio,
       CAST(value AS REAL) / 28 AS real_ratio
FROM measurements
ORDER BY label;
SELECT 'numeric cast', typeof(CAST(21 AS NUMERIC)), CAST(21 AS NUMERIC) / 28;
SELECT 'real cast', typeof(CAST(21 AS REAL)), CAST(21 AS REAL) / 28;

Run:

python3 run-sql.py inspect-numeric-affinity.sql

Output:

A|21|integer|0|0.75
B|24.5|real|0.875|0.875
numeric cast|integer|0
real cast|real|0.75

NUMERIC affinity stores the exactly integral 21.0 as an integer, while 24.5 remains REAL. Consequently, two values in the same column can follow different arithmetic paths. typeof() exposes the value’s storage class rather than repeating the declared type.

CAST(… AS NUMERIC) does not force a floating-point result. CAST(… AS REAL) does. NUMERIC is also not a fixed-precision decimal type, and casting malformed input is not a validation strategy. Validate the input contract separately instead of hoping a cast will reject bad data. See SQLite’s type-affinity rules.

3. Keep bounded cent totals exact until display

Save as keep-small-currency-totals-integral.sql:

CREATE TABLE basket (
    item TEXT PRIMARY KEY,
    unit_cents INTEGER NOT NULL
        CHECK (typeof(unit_cents) = 'integer' AND unit_cents BETWEEN 0 AND 100000),
    quantity INTEGER NOT NULL
        CHECK (typeof(quantity) = 'integer' AND quantity BETWEEN 0 AND 100)
);
INSERT INTO basket VALUES ('Pad', 250, 2), ('Pen', 199, 3);
SELECT item, unit_cents * quantity AS line_cents
FROM basket ORDER BY item;
SELECT SUM(unit_cents * quantity) AS total_cents,
       typeof(SUM(unit_cents * quantity)) AS total_type,
       printf('%d.%02d', SUM(unit_cents * quantity) / 100,
                         SUM(unit_cents * quantity) % 100) AS display_total
FROM basket;
SELECT 'binary fraction equality', 0.1 + 0.2 = 0.3;
SELECT 'integer cent equality', 10 + 20 = 30;

Run:

python3 run-sql.py keep-small-currency-totals-integral.sql

Output:

Pad|500
Pen|597
1097|integer|10.97
binary fraction equality|0
integer cent equality|1

The small products and total remain integers. Integer division extracts whole units, and the remainder supplies two cent digits. This display expression is deliberately limited to nonnegative totals; negative balances need a sign-aware formatter. The formatted total is text, so retain the numeric cents value for calculation and sorting.

The equality checks show why decimal-looking REAL values should not be treated as exact decimal arithmetic. Integer storage still has limits: enforce integer input, bound totals as well as individual rows, and handle overflow. A huge integer SUM can fail, while overflowing scalar arithmetic can switch to approximate REAL values.

SQLite’s floating-point guide explains why binary64 values are approximate. Rounding a display does not change that storage model. The aggregate reference describes SUM overflow and empty-input behavior, and the printf reference documents formatting.

Choose arithmetic intentionally: REAL for approximate ratios, validated bounded integers for exact counts or scaled amounts, and an appropriate decimal implementation when the domain requires exact fractional calculations.

Clean up

In the same terminal, remove only these example files and the empty scratch directory:

rm -- run-sql.py calculate-completion-ratios.sql inspect-numeric-affinity.sql keep-small-currency-totals-integral.sql
cd -- "$start_dir" || exit 1
rmdir -- "$work_dir"
unset start_dir work_dir

Acknowledgements

This post was created with assistance from AI.