Numbers, arithmetic and rounding

Arithmetic in SQL follows the types of its operands. Two whole numbers give a whole number in PostgreSQL and SQLite, so 7 / 2 is 3, while MySQL returns 3.5000. A number with a decimal point is usually a floating-point value, a binary fraction that cannot hold most decimals exactly, which is why 0.1 + 0.2 = 0.3 can be false and why round() can go the wrong way on a value that ends in 5. Money therefore belongs in whole paise or in an exact numeric or DECIMAL column. This lesson shows each of these effects in a query you can run, then compares the three engines, including what each does when a number grows too large or is divided by zero.

Press Run on any example to execute it with SQLite 3.49.1 from sql.js 1.14.2. The comparisons with PostgreSQL 18 and MySQL 8.4 follow their reference manuals.

Division and remainders

-- Division and remainders: whole numbers stay whole, a decimal point makes a REAL.
SELECT 7 / 2    AS int_div,
       7 / 2.0  AS real_div,
       -7 / 2   AS neg_div,
       7 % 3    AS rem,
       -7 % 3   AS neg_rem,
       7 % -3   AS neg_divisor,
       7 / 0    AS by_zero,
       7 % 0    AS rem_by_zero;

┌─────────┬──────────┬─────────┬─────┬─────────┬─────────────┬─────────┬─────────────┐
│ int_div │ real_div │ neg_div │ rem │ neg_rem │ neg_divisor │ by_zero │ rem_by_zero │
├─────────┼──────────┼─────────┼─────┼─────────┼─────────────┼─────────┼─────────────┤
│ 3       │ 3.5      │ -3      │ 1   │ -1      │ 1           │         │             │
└─────────┴──────────┴─────────┴─────┴─────────┴─────────────┴─────────┴─────────────┘

When both sides of / are integers, SQLite and PostgreSQL divide and drop the fraction, moving toward zero: 7 / 2 is 3 and -7 / 2 is -3, not -4. One decimal point is enough to change that, so 7 / 2.0 is 3.5. The remainder % takes the sign of the number being divided, so -7 % 3 is -1 in all three engines. Dividing by zero gives NULL in SQLite, quietly, which a later sum() or avg() then skips.

MySQL does not drop the fraction. With two exact operands its / returns a decimal whose scale is the scale of the first operand plus the setting div_precision_increment, 4 by default, so 7 / 2 prints 3.5000. Integer division has its own operator there, DIV, and 7 DIV 2 is 3. PostgreSQL also has div(7, 2) for the same purpose.

The integer rule causes a classic bug in averages and percentages: paid_orders * 100 / all_orders is a whole number in PostgreSQL and SQLite. The lesson on aggregate functions shows the effect on avg(), and the one on conditional aggregation the fix for percentages: write one operand with a decimal point, such as 100.0.

Floating point: why 0.1 + 0.2 is not 0.3

SQLite’s REAL, PostgreSQL’s double precision and MySQL’s DOUBLE store a number in binary, in 8 bytes. A fraction such as 0.1 has no exact binary form, just as one third has no exact decimal form, so the database stores the nearest binary fraction instead.

-- A REAL is a binary fraction, so most decimals are stored as the nearest one.
SELECT 0.1 + 0.2                     AS shown,
       0.1 + 0.2 = 0.3               AS equal,
       printf('%!.17g', 0.1 + 0.2)   AS stored,
       abs((0.1 + 0.2) - 0.3) < 1e-9 AS close_enough;

-- The same happens to a value that ends in 5, right where rounding decides.
SELECT 8.225                   AS typed,
       printf('%!.20g', 8.225) AS stored,
       round(8.225, 2)         AS rounded;

┌───────┬───────┬─────────────────────┬──────────────┐
│ shown │ equal │       stored        │ close_enough │
├───────┼───────┼─────────────────────┼──────────────┤
│ 0.3   │ 0     │ 0.30000000000000004 │ 1            │
└───────┴───────┴─────────────────────┴──────────────┘
┌───────┬──────────────────────┬─────────┐
│ typed │        stored        │ rounded │
├───────┼──────────────────────┼─────────┤
│ 8.225 │ 8.224999999999999645 │ 8.22    │
└───────┴──────────────────────┴─────────┘

The result table shows 0.3, because the output rounds to 15 significant digits, yet 0.1 + 0.2 = 0.3 is false (0). printf('%!.17g', …) asks for 17 digits and reveals the stored value, 0.30000000000000004. Comparing with a tolerance, as close_enough does, is the way to compare such values.

The second query matters more for money. The literal 8.225 is stored as 8.224999999999999645, a little below the halfway point between 8.22 and 8.23, so round(8.225, 2) returns 8.22. Nothing is wrong with round(): it never saw 8.225.

PostgreSQL and MySQL avoid this for literals: they read 0.1 as an exact decimal (numeric in PostgreSQL, an exact value in MySQL’s precision maths), so SELECT 0.1 + 0.2 = 0.3 is true in both. The surprise comes back as soon as the values sit in double precision or DOUBLE columns, or are written in scientific notation such as 0.1e0 in MySQL.

Rounding, floor, ceil and truncation

-- round(), floor(), ceil() and a cast, on halves and on negative numbers.
CREATE TABLE amounts (x REAL);
INSERT INTO amounts VALUES (2.5), (-2.5), (2.4), (-2.6);

SELECT x,
       round(x)           AS rounded,
       floor(x)           AS floored,
       ceil(x)            AS ceiled,
       CAST(x AS INTEGER) AS toward_zero
FROM amounts;

-- A number of decimal places, and a negative one, which SQLite treats as 0.
SELECT round(3.14159, 2) AS two_places, round(1234.5678, -2) AS minus_two;

┌──────┬─────────┬─────────┬────────┬─────────────┐
│  x   │ rounded │ floored │ ceiled │ toward_zero │
├──────┼─────────┼─────────┼────────┼─────────────┤
│ 2.5  │ 3.0     │ 2       │ 3      │ 2           │
│ -2.5 │ -3.0    │ -3      │ -2     │ -2          │
│ 2.4  │ 2.0     │ 2       │ 3      │ 2           │
│ -2.6 │ -3.0    │ -3      │ -2     │ -2          │
└──────┴─────────┴─────────┴────────┴─────────────┘
┌────────────┬───────────┐
│ two_places │ minus_two │
├────────────┼───────────┤
│ 3.14       │ 1235.0    │
└────────────┴───────────┘

SQLite’s round() sends a half away from zero, so 2.5 becomes 3.0 and -2.5 becomes -3.0. floor() always moves down and ceil() always moves up, so on -2.5 they give -3 and -2. A cast to INTEGER drops the fraction, moving toward zero. The second query shows a portability trap: SQLite treats a negative number of decimal places as 0, so round(1234.5678, -2) is 1235.0, where PostgreSQL and MySQL return 1200.

Halves are where the engines differ most:

PostgreSQL has no two-argument round() for double precision, so round(x, 2) on such a column is an error until you cast it, round(x::numeric, 2). MySQL’s manual says the same ROUND(2.5) returns 3 for the exact literal and 2 for 25E-1, a floating-point one. The safe rule in every engine: round exact values, and keep money exact.

Money: whole paise or exact decimals

The fix for money is to never store it as a binary fraction. In PostgreSQL and MySQL use numeric(p, s) or DECIMAL(p, s), which add, subtract and multiply decimals exactly. In SQLite, which has no exact decimal type, store whole paise in an INTEGER column and do the arithmetic with integers:

-- Tax at 5 % on a subtotal of 164.50 rupees: exact tax is 822.5 paise, so 823 after rounding half up.
CREATE TABLE lines (item TEXT, price_rupees REAL, price_paise INTEGER, rate_percent INTEGER);
INSERT INTO lines VALUES ('Masala tea 1 kg', 164.50, 16450, 5);

SELECT item,
       round(price_rupees * rate_percent / 100, 2) AS tax_from_real,
       (price_paise * rate_percent + 50) / 100     AS tax_paise
FROM lines;

-- Rupees for display, from whole paise, with no floating point at all.
SELECT tax_paise,
       printf('%d.%02d', tax_paise / 100, tax_paise % 100) AS tax_rupees
FROM (SELECT (price_paise * rate_percent + 50) / 100 AS tax_paise FROM lines);

┌─────────────────┬───────────────┬───────────┐
│      item       │ tax_from_real │ tax_paise │
├─────────────────┼───────────────┼───────────┤
│ Masala tea 1 kg │ 8.22          │ 823       │
└─────────────────┴───────────────┴───────────┘
┌───────────┬────────────┐
│ tax_paise │ tax_rupees │
├───────────┼────────────┤
│ 823       │ 8.23       │
└───────────┴────────────┘

With REAL rupees the tax comes out at 8.22, because the product is stored just below 8.225, as in the previous section. With paise, (16450 * 5 + 50) / 100 is exact: integer division drops the fraction, and adding 50 first turns that into rounding half up, giving 823 paise. The second query turns paise into rupees for display without any floating point: / 100 gives the rupees, % 100 the paise, and %02d keeps the leading zero of an amount such as .05. The trick works for amounts that are never negative; a refund needs its own rule, because integer division moves negative numbers toward zero.

Overflow: when a number gets too large

Integers have a largest value: 9223372036854775807 for a 64-bit integer, about 9.2 × 10¹⁸. What happens one step beyond it depends on the engine and on the operation:

-- One more than the largest 64-bit integer, by + and by sum().
SELECT 9223372036854775807 + 1         AS plus_one,
       typeof(9223372036854775807 + 1) AS type;

CREATE TABLE big (n INTEGER);
INSERT INTO big VALUES (9223372036854775807), (1);

SELECT total(n) AS with_total FROM big;
SELECT sum(n) AS with_sum FROM big;

┌──────────────────────┬──────┐
│       plus_one       │ type │
├──────────────────────┼──────┤
│ 9.22337203685478e+18 │ real │
└──────────────────────┴──────┘
┌──────────────────────┐
│      with_total      │
├──────────────────────┤
│ 9.22337203685478e+18 │
└──────────────────────┘

Runtime error near line 9: integer overflow

SQLite’s + quietly switches to a REAL, and the result loses its last digits, as 9.22337203685478e+18 shows. sum() refuses instead and stops with “integer overflow”, while total() adds in floating point and never overflows. PostgreSQL and MySQL stop with an error on the +: “bigint out of range” and error 1690 “BIGINT value is out of range”. Their sum() returns a wider type, numeric in PostgreSQL and DECIMAL in MySQL, so summing big integers does not overflow there.

Math functions in your browser and elsewhere

The SQLite that runs this track’s examples was built with its own set of math functions, which differ from both the optional math functions of SQLite itself and from the other engines:

-- Math functions of the SQLite in your browser (sql.js 1.14.2).
SELECT power(2, 10)  AS power,
       sqrt(2)       AS root,
       log10(1000)   AS log10,
       log(100)      AS log,
       exp(1)        AS e,
       sign(-5)      AS sign;

-- max() with several arguments is not the aggregate: it compares its arguments.
SELECT max(4, 9, 2) AS largest, max(4, NULL, 2) AS with_null;

SELECT pow(2, 10) AS pow;

┌────────┬─────────────────┬───────┬──────────────────┬──────────────────┬──────┐
│ power  │      root       │ log10 │       log        │        e         │ sign │
├────────┼─────────────────┼───────┼──────────────────┼──────────────────┼──────┤
│ 1024.0 │ 1.4142135623731 │ 3.0   │ 4.60517018598809 │ 2.71828182845905 │ -1   │
└────────┴─────────────────┴───────┴──────────────────┴──────────────────┴──────┘
┌─────────┬───────────┐
│ largest │ with_null │
├─────────┼───────────┤
│ 9       │           │
└─────────┴───────────┘

Parse error near line 12: no such function: pow

In this browser log() is the natural logarithm and pow() does not exist, while SQLite’s own math library (when it is compiled in) and PostgreSQL define log() as base 10, and MySQL’s LOG() is natural. log10() means the same in all of them, so use it, or exp() and power(), when a query must run everywhere. The multi-argument max(4, NULL, 2) returns NULL as soon as one argument is NULL. PostgreSQL’s greatest() ignores NULLs instead, and MySQL’s GREATEST() returns NULL like SQLite’s max(), so wrap nullable arguments in coalesce() when the answer must be the same everywhere.

Your turn

-- Each order's total including its tax: total_paise (whole paise) and
-- total_rupees (text with two decimals), using whole numbers only.
SELECT id,
       subtotal_paise AS total_paise,
       '' AS total_rupees
FROM orders;

# Sample tests: each one builds a small orders table, runs your query on it and compares the rows it returns (in any
# order).
tests:
  - name: adds the tax to each subtotal
    setup: |
      CREATE TABLE orders (id INTEGER PRIMARY KEY, subtotal_paise INTEGER NOT NULL, gst_percent INTEGER NOT NULL);
      INSERT INTO orders VALUES (1, 10000, 18), (2, 16450, 5), (3, 99900, 40), (4, 500, 0);
    expected:
      columns: [id, total_paise, total_rupees]
      rows:
        - [1, 11800, '118.00']
        - [2, 17273, '172.73']
        - [3, 139860, '1398.60']
        - [4, 500, '5.00']
  - name: rounds half a paisa up and smaller fractions to the nearest paisa
    setup: |
      CREATE TABLE orders (id INTEGER PRIMARY KEY, subtotal_paise INTEGER NOT NULL, gst_percent INTEGER NOT NULL);
      INSERT INTO orders VALUES (1, 1075, 18), (2, 25, 18), (3, 1074, 18), (4, 10, 5), (5, 9, 5);
    expected:
      columns: [id, total_paise, total_rupees]
      rows:
        - [1, 1269, '12.69']
        - [2, 30, '0.30']
        - [3, 1267, '12.67']
        - [4, 11, '0.11']
        - [5, 9, '0.09']
  - name: handles tiny and very large orders
    setup: |
      CREATE TABLE orders (id INTEGER PRIMARY KEY, subtotal_paise INTEGER NOT NULL, gst_percent INTEGER NOT NULL);
      INSERT INTO orders VALUES (1, 123456789, 40), (2, 1, 40), (3, 2, 40);
    expected:
      columns: [id, total_paise, total_rupees]
      rows:
        - [1, 172839505, '1728395.05']
        - [2, 1, '0.01']
        - [3, 3, '0.03']

Interview questions

Fresher · Why should money be stored as whole paise or an exact decimal type, never as FLOAT or REAL?

A floating-point column stores a binary fraction, and most decimal amounts, such as 0.10 or 8.225, have no exact binary form, so the database keeps the nearest value instead. The difference is tiny, but it shows up exactly where money is sensitive: 0.1 + 0.2 = 0.3 is false, sums drift across many rows, and round(8.225, 2) gives 8.22 because the stored value is just below the halfway point. An exact type, numeric in PostgreSQL or DECIMAL in MySQL, does decimal arithmetic without that error, and whole paise in an integer column are exact in every engine, including SQLite, which has no decimal type. Convert to rupees only for display, and decide the rounding rule, such as half up to the paisa, in one place.

Key takeaways

• Integer divided by integer is a whole number in PostgreSQL and SQLite (7 / 2 is 3) but 3.5000 in MySQL, which has DIV for whole-number division.
• Division by zero is NULL in SQLite and in MySQL’s SELECT, but an error in PostgreSQL.
• Floating-point values are binary fractions: 0.1 + 0.2 = 0.3 is false, and round(8.225, 2) gives 8.22.
• PostgreSQL and MySQL round exact halves away from zero, but floating halves usually to the even neighbour; SQLite rounds every half away from zero.
• Keep money in whole paise or numeric/DECIMAL, round with integer arithmetic, and format rupees only for display.
• Overflow is an error in PostgreSQL and MySQL; in SQLite + silently becomes a REAL and sum() fails, while total() never overflows.