Numeric Functions

Numeric Functions

SQL functions also work on numbers.

ROUND

Round a number to a given number of decimals:

SELECT ROUND(235.548, 1);   -- 235.5
SELECT ROUND(235.548, 0);   -- 236

CEILING and FLOOR

• CEILING rounds up to the nearest whole number.
• FLOOR rounds down.

SELECT CEILING(25.1);   -- 26
SELECT FLOOR(25.9);     -- 25

ABS

Absolute value (removes the minus sign):

SELECT ABS(-17);   -- 17

SQRT

Square root:

SELECT SQRT(64);   -- 8

MOD

Remainder of a division (MySQL / PostgreSQL):

SELECT MOD(10, 3);   -- 1

PI and POWER

SELECT PI();                  -- 3.141592...
SELECT POWER(2, 3);           -- 8 (2 to the power of 3)

TL;DR

• ROUND rounds to decimals; CEILING up; FLOOR down.
• ABS drops the sign; SQRT finds the square root.
• MOD gives the remainder; POWER raises to a power.