SQLite Math Functions
SQLite provides a set of built-in math functions for rounding, absolute values, powers, roots, logarithms, and trigonometry. These functions work directly inside SELECT, WHERE, and UPDATE statements — no external code needed.
Why Math Functions Matter in SQL
WITHOUT math functions: WITH math functions: ────────────────────────────────── ────────────────────────────────── Pull all rows into your app Do the math in the database Compute in Python/JS/Java Return already-computed results Two round trips: fetch + compute One query, one result set More code, more network load Less code, faster response
Sample Table
CREATE TABLE products ( id INTEGER PRIMARY KEY, name TEXT, price REAL, discount REAL, -- percentage, e.g. 15 means 15% quantity INTEGER ); INSERT INTO products VALUES (1, 'Laptop', 75000, 10, 5), (2, 'Phone', 25000, 5, 12), (3, 'Headphones', 3500, 20, 8), (4, 'Charger', 1299, 0, 30), (5, 'USB Hub', 2499, 12, 0);
ROUND() — Rounding Numbers
-- Round to 2 decimal places
SELECT name,
price * (1 - discount / 100.0) AS raw_price,
ROUND(price * (1 - discount / 100.0), 2) AS discounted_price
FROM products;
name │ raw_price │ discounted_price
────────────┼──────────────┼─────────────────
Laptop │ 67500.0 │ 67500.00
Phone │ 23750.0 │ 23750.00
Headphones │ 2800.0 │ 2800.00
Charger │ 1299.0 │ 1299.00
USB Hub │ 2199.12 │ 2199.12
-- Round to nearest integer (0 decimal places)
SELECT ROUND(3.567, 0); -- 4.0
SELECT ROUND(3.4, 0); -- 3.0
SELECT ROUND(3.5, 0); -- 4.0
ABS() — Absolute Value
-- Remove negative sign from a value
SELECT ABS(-42); -- 42
SELECT ABS(42); -- 42
SELECT ABS(-3.14); -- 3.14
-- Use case: find how far each price deviates from 10000
SELECT name, price,
ABS(price - 10000) AS distance_from_10k
FROM products
ORDER BY distance_from_10k;
name │ price │ distance_from_10k
────────────┼────────┼──────────────────
Phone │ 25000 │ 15000
Headphones │ 3500 │ 6500
USB Hub │ 2499 │ 7501
Charger │ 1299 │ 8701
Laptop │ 75000 │ 65000
CEIL() and FLOOR() — Ceiling and Floor
-- CEIL: round UP to the next integer
SELECT CEIL(3.1); -- 4
SELECT CEIL(3.9); -- 4
SELECT CEIL(-3.1); -- -3 (towards zero)
-- FLOOR: round DOWN to the previous integer
SELECT FLOOR(3.9); -- 3
SELECT FLOOR(3.1); -- 3
SELECT FLOOR(-3.1); -- -4 (away from zero)
-- Use case: ceiling division for packaging
-- How many boxes of 6 are needed for each product quantity?
SELECT name, quantity,
CEIL(quantity / 6.0) AS boxes_needed
FROM products;
name │ quantity │ boxes_needed
────────────┼──────────┼─────────────
Laptop │ 5 │ 1
Phone │ 12 │ 2
Headphones │ 8 │ 2
Charger │ 30 │ 5
USB Hub │ 0 │ 0
POWER() and SQRT() — Exponents and Roots
-- POWER(base, exponent) SELECT POWER(2, 10); -- 1024 (2 to the 10th) SELECT POWER(3, 3); -- 27 (3 cubed) SELECT POWER(4, 0.5); -- 2.0 (square root of 4) -- SQRT: square root SELECT SQRT(144); -- 12.0 SELECT SQRT(2); -- 1.4142135... -- Use case: Euclidean distance between two products' price points SELECT SQRT(POWER(75000 - 25000, 2) + POWER(10 - 5, 2)) AS price_distance; -- 50000.000...
LOG() — Logarithms
-- LOG(x) → natural log (base e) -- LOG(base, x) → log with custom base -- LOG10(x) → base-10 log SELECT LOG(2.71828); -- ~1.0 (natural log of e) SELECT LOG(10, 1000); -- 3.0 (log base 10 of 1000) SELECT LOG10(1000); -- 3.0 SELECT LOG(2, 1024); -- 10.0 (how many times to double to reach 1024)
Trigonometric Functions
-- All trig functions work in RADIANS SELECT SIN(0); -- 0.0 SELECT COS(0); -- 1.0 SELECT TAN(0); -- 0.0 SELECT SIN(3.14159 / 2); -- ~1.0 (sin of 90 degrees) -- Convert degrees to radians first: -- radians = degrees × π / 180 SELECT SIN(90 * 3.14159265 / 180); -- ~1.0 -- PI constant approximation SELECT PI(); -- 3.14159265358979 (SQLite 3.38+)
MOD() and Integer Division
-- Modulo: remainder after division SELECT 10 % 3; -- 1 (10 ÷ 3 = 3 remainder 1) SELECT 15 % 5; -- 0 (15 ÷ 5 = 3 remainder 0) SELECT MOD(10, 3); -- 1 (same as %) -- Use case: find even-numbered product IDs SELECT id, name FROM products WHERE id % 2 = 0; id │ name ───┼────────── 2 │ Phone 4 │ Charger -- Integer division (truncate decimal) SELECT 10 / 3; -- 3 (integer ÷ integer = integer in SQLite) SELECT 10.0 / 3; -- 3.333... (float ÷ integer = float)
MAX() and MIN() as Scalar Clamps
-- Clamp discount between 0 and 50
SELECT name, discount,
MAX(0, MIN(50, discount)) AS clamped_discount
FROM products;
name │ discount │ clamped_discount
────────────┼──────────┼─────────────────
Laptop │ 10 │ 10
Phone │ 5 │ 5
Headphones │ 20 │ 20
Practical Use: Revenue Report with Math
SELECT name, quantity, price, discount, ROUND(price * (1 - discount / 100.0), 2) AS unit_price, ROUND(price * (1 - discount / 100.0) * quantity, 2) AS total_revenue FROM products WHERE quantity > 0 ORDER BY total_revenue DESC; name │ qty │ price │ disc │ unit_price │ total_revenue ────────────┼─────┼───────┼──────┼────────────┼────────────── Phone │ 12 │ 25000 │ 5 │ 23750.00 │ 285000.00 Laptop │ 5 │ 75000 │ 10 │ 67500.00 │ 337500.00 Charger │ 30 │ 1299 │ 0 │ 1299.00 │ 38970.00 Headphones │ 8 │ 3500 │ 20 │ 2800.00 │ 22400.00
Math Functions Quick Reference
FUNCTION DESCRIPTION EXAMPLE ────────────────────────────────────────────────────────────── ABS(x) Absolute value ABS(-5) → 5 ROUND(x, n) Round to n decimal places ROUND(3.567,2) → 3.57 CEIL(x) Round up CEIL(3.1) → 4 FLOOR(x) Round down FLOOR(3.9) → 3 POWER(x, y) x to the power y POWER(2,8) → 256 SQRT(x) Square root SQRT(25) → 5 LOG(x) Natural logarithm LOG(1) → 0 LOG(b, x) Log base b of x LOG(10,100) → 2 LOG10(x) Base-10 logarithm LOG10(1000) → 3 SIN(x) Sine (radians) SIN(0) → 0 COS(x) Cosine (radians) COS(0) → 1 TAN(x) Tangent (radians) TAN(0) → 0 PI() Pi constant (3.38+) PI() → 3.14159... x % y Remainder 10 % 3 → 1 MOD(x, y) Modulo (same as %) MOD(10,3) → 1
Key Takeaways
ROUND(x, n)rounds to n decimal places; use it for prices and percentagesABS(x)removes the sign from a number — useful for calculating deviationsCEIL(x)rounds up;FLOOR(x)rounds down — useful for packaging and page countsPOWER(x, y)andSQRT(x)handle exponents and square roots- Integer division truncates:
10 / 3 = 3; use10.0 / 3to get a decimal result