Math functions
Math functions round numbers, compute powers and roots, and find remainders.
In this page:
Syntax
SELECT ROUND(x, n), CEIL(x), FLOOR(x), ABS(x), POWER(x, y), SQRT(x)
FROM table_name;Math functions
ROUND(x, n), CEIL, FLOOR, TRUNC, ABS, POWER, SQRT, MOD (or %), SIGN, RANDOM and LN are built in. Integer division truncates, so cast to numeric to get decimals. aggregates such as SUM, AVG, MIN and MAX work across rows.
Note that the SQLite version used in the site editor lacks some of these, so they are shown in psql.
Note:
ROUND with a negative second argument rounds to tens, hundreds and so on.
Example: Math functions
shop=# SELECT ROUND(3.14159, 2) AS r, CEIL(2.1) AS c, FLOOR(2.9) AS f, TRUNC(-2.9) AS t, ABS(-7) AS a;
r | c | f | t | a
------+---+---+----+---
3.14 | 3 | 2 | -2 | 7
shop=# SELECT POWER(2, 10) AS p, SQRT(144) AS s, MOD(17, 5) AS m, 17 % 5 AS m2;
p | s | m | m2
-------+----+---+----
1024 | 12 | 2 | 2
shop=# SELECT ROUND(1234.5678, -2) AS to_hundreds, ROUND(2.5) AS r1, ROUND(3.5) AS r2;
to_hundreds | r1 | r2
-------------+----+----
1200 | 3 | 4⚠️ Run this in your own terminal or Node.js environment.
Related Topics
Common Mistakes
- Expecting integer division to keep decimals
- Rounding money with floats
- Using RANDOM without a seed for reproducible tests
Chapter Summary
- ROUND, CEIL, FLOOR, TRUNC
- ABS, POWER, SQRT
- MOD or % for remainders
- Cast to numeric for decimals
🔒
Chapter Quiz — Complete all 7 topics to unlock
0/7 topics done
Complete these topics first: