Numeric & Math Functions

Beyond basic arithmetic (+, -, *, /), SQL provides a standard toolbox of numeric functions for rounding, absolute values, powers, roots, and remainders. Unlike string functions, these are fairly consistent across the major databases — the main thing to watch for is the difference between CEIL()/CEILING() naming and how integer vs. floating-point division behaves.

Common numeric functions

Function	Description	Example
`ROUND(n, d)`	Rounds `n` to `d` decimal places (default 0)	`ROUND(19.987, 2)` → `19.99`
`CEIL(n)` / `CEILING(n)`	Rounds up to the nearest integer	`CEIL(4.1)` → `5`
`FLOOR(n)`	Rounds down to the nearest integer	`FLOOR(4.9)` → `4`
`ABS(n)`	Absolute (non-negative) value	`ABS(-7)` → `7`
`POWER(n, p)`	Raises `n` to the power `p`	`POWER(2, 10)` → `1024`
`SQRT(n)`	Square root of `n`	`SQRT(81)` → `9`
`MOD(n, m)` / `n % m`	Remainder of `n` divided by `m`	`MOD(17, 5)` → `2`

Rounding

ROUND() takes an optional second argument for how many decimal places to keep. A negative value rounds to the left of the decimal point (tens, hundreds, and so on):

ROUND examples

SQL

SELECT
  ROUND(19.987, 2) AS to_cents,   -- 19.99
  ROUND(1234, -2)  AS to_hundred; -- 1200

CEIL / FLOOR / ABS

Ceiling, floor, absolute value

SQL

SELECT
  CEILING(4.1)  AS ceiling_result, -- 5
  FLOOR(4.9)    AS floor_result,   -- 4
  ABS(-42)      AS absolute_value; -- 42

POWER, SQRT, and MOD

Power, square root, modulo

SQL

SELECT
  POWER(2, 10)   AS two_to_the_ten, -- 1024
  SQRT(81)       AS square_root,    -- 9
  MOD(17, 5)     AS remainder,      -- 2
  17 % 5         AS remainder_op;   -- 2 (most dialects support the % operator too)

Practical example — rounding calculated prices

A very common real-world need is applying a discount or tax rate to a price and rounding the result to a sensible number of decimal places for currency:

Rounding a discounted price to 2 decimal places

SQL

SELECT
  product_name,
  price,
  ROUND(price * 0.85, 2) AS discounted_price -- 15% off, rounded to cents
FROM products;

Dialect note

Most databases support both CEIL() and CEILING() as aliases for the same function, but a few older or more minimal engines only implement one of the two — check your database's documentation if a query fails on an unfamiliar engine.