Numeric Functions

Numeric functions round prices, find absolute differences, and calculate powers and roots. This lesson also explains why decimal calculations sometimes produce results like 2398.7999999999997 — and how to store money so they don't.

Rounding

SELECT round(19.987, 2) AS two_places,
       round(19.5) AS nearest_whole,
       ceil(19.01) AS rounded_up,
       floor(19.99) AS rounded_down;

round(x, n) rounds to n decimal places (default 0). ceil (or ceiling) always goes up and floor always goes down. Rounding of exact halves (2.5) differs between databases and data types, so don't rely on it for financial rules — make the rule explicit.

Floating-point surprises

SELECT 0.1 + 0.2 AS looks_wrong, 19.99 * 120 AS also_odd;

Values like 0.1 can't be stored exactly in binary floating point (REAL, FLOAT, DOUBLE), so tiny errors appear. SQLite stores all decimals this way. Other databases have exact DECIMAL/NUMERIC types, where 0.1 + 0.2 is exactly 0.3.

SELECT round(0.1 + 0.2, 2) AS tidy, round(19.99 * 120, 2) AS also_tidy;

Absolute value and sign

SELECT first_name, salary,
       salary - 65000 AS difference,
       abs(salary - 65000) AS distance_from_65k,
       sign(salary - 65000) AS direction
FROM employees
WHERE department_id = 1;

Powers, roots and other maths

SELECT power(2, 10) AS two_to_ten,
       sqrt(144) AS root,
       pi() AS pi,
       log10(1000) AS log_ten;

Most databases have a full maths library: power, sqrt, exp, ln, log, trigonometric functions and more. (SQLite includes them only when compiled with its maths extension; the build used here includes the common ones.)

Remainders

SELECT id, id % 3 AS group_number
FROM orders
WHERE id <= 6;

% gives the remainder after division — handy for splitting rows into buckets or finding even numbers (id % 2 = 0). The standard function form is MOD(a, b) (PostgreSQL, MySQL, Oracle).

Random numbers

SELECT name FROM products ORDER BY random() LIMIT 3;

Ordering by a random value picks random rows — fine for small tables, slow for big ones. The function is random() in SQLite and PostgreSQL, RAND() in MySQL and NEWID() for ordering in SQL Server. Run it in the editor below a few times to see different results.

Formatting numbers

SELECT name, format('€%.2f', price) AS price_label
FROM products
WHERE category = 'Toys';

Formatting for display — currency symbols, thousands separators — is usually better done in the application, which knows the user's locale. When you need it in SQL, use format/printf (SQLite), to_char (PostgreSQL, Oracle), FORMAT (MySQL, SQL Server).

Try it yourself

Calculate each product's price including 23% VAT, rounded to two decimals, and pick three random products.