Mathematical Functions

The everyday numeric toolkit: rounding, roots and powers, logarithms, and the row-wise greatest / least. Operators (+, -, *, /) live on the Arithmetic Operators page; everything here is written as a function call, deliberately — mod(x, 2) reads better than x % 2 for an audience that reads % as "percent".

Rounding: round, floor, ceil, trunc

Four ways to drop decimals, and the difference matters:

round(19.99)      →  20     nearest
floor(19.99)      →  19     always down
ceil(19.01)       →  20     always up
trunc(19.99)      →  19     always toward zero

floor and trunc agree on positive numbers and part company on negatives: floor(-1.5) is -2 (further down), trunc(-1.5) is -1 (toward zero). Use trunc when a value must never be inflated — a fee, a quota — and round when it should simply be nearest.

round and trunc both take an optional number of decimal places: round(x, 2), trunc(x, 2).

One caveat, unavoidable. Half-way rounding of values held as binary floats is decided by the database, not by KQL: 0.15 is stored as 0.149999…, so an engine may round it either way. Where that matters, keep the value a decimal rather than a float.

Logarithms: ln, log10, log

There are three, and the naming is deliberate:

ln(x)          natural logarithm, base e
log10(x)       base 10
log(base, x)   any base — log(2, 8) is 3

There is no one-argument log, and that is not an omission. Every engine accepts log(100) and they do not agree what it means: PostgreSQL and SQLite read it as base 10 and return 2, MariaDB and SQL Server read it as the natural log and return 4.605. A name that means two things cannot be fixed by translating it, only by not offering it — so say which one you want, and ln or log10 will mean the same thing on every database.

With two arguments the base comes first, as it reads aloud: "log base 2 of 8".

greatest and least

These compare across the columns of one row — not down a column:

FIND order_details od
FETCH greatest(od.unit_price, 20.0) at_least_twenty

That is the row's own price, or 20 if the price is lower. The functions that collapse many rows into one are max and min, on the Aggregate Functions page. It is an easy pair to confuse, and the two answer completely different questions.

One caveat, and this one is not cosmetic. As soon as an argument can be blank, the engines part company. With greatest(NULL, 3), PostgreSQL, DuckDB and SQL Server answer 3 — they ignore blanks and take the largest of the rest — while MariaDB, Oracle, Snowflake, SQLite and Trino answer blank, because the SQL standard lets a blank win the moment it appears. Both camps are defensible; PostgreSQL documents its deviation openly.

KQL does not paper over it. Levelling it would mean rewriting one camp's SQL, and with a variable number of arguments that needs a replacement construct rather than a translation — a lot of machinery to hide a difference the query itself can state. So say which you mean: wrap each argument in coalesce with a neutral value to ignore blanks, or keep the blanks in the call and let them win everywhere. If the arguments cannot be blank, none of this applies and the functions are portable.

Division and remainders

mod(x, y) is the remainder. Like /, a zero divisor yields a blank rather than an error — so a report will not fail on one bad row. This is worth knowing because SQL itself is wildly inconsistent here: left alone, three of the supported engines raise an error, three return a blank, and Oracle returns the dividend unchanged, which looks like a real answer and is not one.

Trigonometry

sin, cos, tan, asin, acos and atan are present and take radians. They are not the common case for this audience, and the wider set — hyperbolics, cot, atan2, degrees/radians — is deliberately not included; see math-review.md for the measurements behind that.

abs

abs(value: numeric) → argument-dependent

Absolute value.

Sample query:

// abs: absolute value of a number.
FIND orders o
FETCH o.order_id ASC, abs(-17.4) magnitude
LIMIT 20

Generated SQL

duckdb · oracle · snowflake · postgresql · mariadb · trino

-- abs: absolute value of a number.
SELECT
  o.order_id
, abs(-17.4) AS magnitude
FROM
 orders o
ORDER BY
  o.order_id ASC
FETCH FIRST 20 ROWS ONLY

The remaining dialects differ only in this expression:

ceil

ceil(value: numeric) → argument-dependent

Nearest integer greater than or equal to value.

Sample query:

// ceil: round freight up to a whole number.
FIND orders o
FETCH o.order_id ASC, ceil(o.freight) rounded_up
LIMIT 20

Generated SQL

duckdb · oracle · snowflake · postgresql · mariadb · trino

-- ceil: round freight up to a whole number.
SELECT
  o.order_id
, ceil(o.freight) AS rounded_up
FROM
 orders o
ORDER BY
  o.order_id ASC
FETCH FIRST 20 ROWS ONLY

The remaining dialects differ only in this expression:

mssql

-- ceil: round freight up to a whole number.
SELECT
  o.order_id
, CEILING(o.freight) AS rounded_up
FROM
 orders o
ORDER BY
  o.order_id ASC
OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY

floor

floor(value: numeric) → argument-dependent

Nearest integer less than or equal to value.

Sample query:

// floor: round freight down to a whole number.
FIND orders o
FETCH o.order_id ASC, floor(o.freight) rounded_down
LIMIT 20

Generated SQL

duckdb · oracle · snowflake · postgresql · mariadb · trino

-- floor: round freight down to a whole number.
SELECT
  o.order_id
, floor(o.freight) AS rounded_down
FROM
 orders o
ORDER BY
  o.order_id ASC
FETCH FIRST 20 ROWS ONLY

The remaining dialects differ only in this expression:

round

round(value: numeric) → argument-dependent

Rounds to the nearest whole number.

round(value: numeric, scale: INTEGER) → argument-dependent

Rounds to scale decimal places.

Half-way rounding of binary-float inputs is engine-defined: a value such as 0.15 is stored as 0.149999…, so engines may round it up or down.

See: What Every Computer Scientist Should Know About Floating-Point Arithmetic.

Sample query:

// round: order freight rounded to one decimal place.

FIND orders o
FILTER o.customer_id = 'TOMSP'
FETCH round(o.freight, 1) freight_rounded

Generated SQL

duckdb · oracle · snowflake · mssql · mariadb · sqlite · trino

-- round: order freight rounded to one decimal place.
SELECT
  round(o.freight, 1) AS freight_rounded
FROM
 orders o
WHERE
  o.customer_id = 'TOMSP'

The remaining dialects differ only in this expression:

trunc

trunc(value: numeric) → argument-dependent

Truncates toward zero — 1.99 becomes 1, and -1.99 becomes -1. Unlike round it never carries, so use it where a value must not be inflated and round where it should be nearest.

trunc(value: numeric, scale: INTEGER) → argument-dependent

Truncates toward zero, keeping scale decimal places.

Sample query:

// trunc: truncate toward zero, never rounding up.
FIND order_details od
FETCH od.order_id ASC, od.product_id ASC, trunc(od.unit_price) whole_price, trunc(19.99, 1) one_decimal
LIMIT 20

Generated SQL

duckdb · oracle · snowflake

-- trunc: truncate toward zero, never rounding up.
SELECT
  od.order_id
, od.product_id
, trunc(od.unit_price) AS whole_price
, trunc(19.99, 1) AS one_decimal
FROM
 order_details od
ORDER BY
  od.order_id ASC
, od.product_id ASC
FETCH FIRST 20 ROWS ONLY

The remaining dialects differ only in this expression:

mariadb

-- trunc: truncate toward zero, never rounding up.
SELECT
  od.order_id
, od.product_id
, TRUNCATE(od.unit_price, 0) AS whole_price
, TRUNCATE(19.99, 1) AS one_decimal
FROM
 order_details od
ORDER BY
  od.order_id ASC
, od.product_id ASC
FETCH FIRST 20 ROWS ONLY

mssql

-- trunc: truncate toward zero, never rounding up.
SELECT
  od.order_id
, od.product_id
, ROUND(od.unit_price, 0, 1) AS whole_price
, ROUND(19.99, 1, 1) AS one_decimal
FROM
 order_details od
ORDER BY
  od.order_id ASC
, od.product_id ASC
OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY

sqlite

-- trunc: truncate toward zero, never rounding up.
SELECT
  od.order_id
, od.product_id
, trunc(od.unit_price) AS whole_price
, (CAST(19.99 * pow(10, 1) AS INTEGER) / pow(10, 1)) AS one_decimal
FROM
 order_details od
ORDER BY
  od.order_id ASC
, od.product_id ASC
LIMIT 20

trino

-- trunc: truncate toward zero, never rounding up.
SELECT
  od.order_id
, od.product_id
, truncate(od.unit_price) AS whole_price
, truncate(CAST(19.99 AS DECIMAL(38,10)), 1) AS one_decimal
FROM
 order_details od
ORDER BY
  od.order_id ASC
, od.product_id ASC
FETCH FIRST 20 ROWS ONLY

mod

mod(dividend: numeric, divisor: numeric) → argument-dependent

Remainder of dividend / divisor. A zero divisor yields no value rather than an error, as with /.

Sample query:

// mod: order id modulo 7.
FIND orders o
FETCH o.order_id ASC, mod(o.order_id, 7) bucket
LIMIT 20

Generated SQL

duckdb · oracle · snowflake · postgresql · mariadb · trino

-- mod: order id modulo 7.
SELECT
  o.order_id
, mod(o.order_id, NULLIF(7, 0)) AS bucket
FROM
 orders o
ORDER BY
  o.order_id ASC
FETCH FIRST 20 ROWS ONLY

The remaining dialects differ only in this expression:

mssql

-- mod: order id modulo 7.
SELECT
  o.order_id
, ((o.order_id) % NULLIF((7), 0)) AS bucket
FROM
 orders o
ORDER BY
  o.order_id ASC
OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY

sign

sign(value: numeric) → INTEGER

Sign of the argument: -1, 0 or 1.

greatest

greatest(value: ORDERED, more: ORDERED, ...) → argument-dependent

Largest of the arguments, compared across the columns of one row — not to be confused with max, which collapses rows into one.

Not portable when an argument can be NULL. The SQL standard lets NULL win: the result is NULL as soon as any argument is NULL. PostgreSQL deviates and ignores NULL arguments, returning the largest (or smallest) of the rest.

With one NULL argument — greatest(NULL, 3):

koryki does not paper over this: unifying it would mean rewriting one side, and with a variable number of arguments only through a replacement construct. Make the intent explicit instead — wrap each argument in coalesce with a neutral value to ignore NULLs, or keep them out of the call to let NULL win everywhere.

See: PostgreSQL — GREATEST and LEAST.

Sample query:

// greatest: the larger of two values in the same row.
FIND order_details od
FETCH od.order_id ASC, od.product_id ASC, round(greatest(od.unit_price, 20.0), 2) at_least_twenty
LIMIT 20

Generated SQL

duckdb · oracle · snowflake · mariadb · trino

-- greatest: the larger of two values in the same row.
SELECT
  od.order_id
, od.product_id
, round(greatest(od.unit_price, 20), 2) AS at_least_twenty
FROM
 order_details od
ORDER BY
  od.order_id ASC
, od.product_id ASC
FETCH FIRST 20 ROWS ONLY

The remaining dialects differ only in this expression:

sqlite

-- greatest: the larger of two values in the same row.
SELECT
  od.order_id
, od.product_id
, round(max(od.unit_price, 20), 2) AS at_least_twenty
FROM
 order_details od
ORDER BY
  od.order_id ASC
, od.product_id ASC
LIMIT 20

least

least(value: ORDERED, more: ORDERED, ...) → argument-dependent

Smallest of the arguments, compared across the columns of one row — the mirror of greatest, and not to be confused with min.

Not portable when an argument can be NULL. The SQL standard lets NULL win: the result is NULL as soon as any argument is NULL. PostgreSQL deviates and ignores NULL arguments, returning the largest (or smallest) of the rest.

With one NULL argument — greatest(NULL, 3):

koryki does not paper over this: unifying it would mean rewriting one side, and with a variable number of arguments only through a replacement construct. Make the intent explicit instead — wrap each argument in coalesce with a neutral value to ignore NULLs, or keep them out of the call to let NULL win everywhere.

See: PostgreSQL — GREATEST and LEAST.

Sample query:

// least: the smaller of two values in the same row.
FIND order_details od
FETCH od.order_id ASC, od.product_id ASC, least(od.quantity, 10) capped_quantity
LIMIT 20

Generated SQL

duckdb · oracle · snowflake · postgresql · mariadb · trino

-- least: the smaller of two values in the same row.
SELECT
  od.order_id
, od.product_id
, least(od.quantity, 10) AS capped_quantity
FROM
 order_details od
ORDER BY
  od.order_id ASC
, od.product_id ASC
FETCH FIRST 20 ROWS ONLY

The remaining dialects differ only in this expression:

sqlite

-- least: the smaller of two values in the same row.
SELECT
  od.order_id
, od.product_id
, min(od.quantity, 10) AS capped_quantity
FROM
 order_details od
ORDER BY
  od.order_id ASC
, od.product_id ASC
LIMIT 20

power

power(base: numeric, exponent: numeric) → FLOAT

base raised to the power of exponent.

Sample query:

// power: freight squared.
FIND orders o
FETCH power(o.freight, 2) squared

Generated SQL

duckdb · oracle · snowflake · postgresql · mariadb · sqlite · trino

-- power: freight squared.
SELECT
  power(o.freight, 2) AS squared
FROM
 orders o

The remaining dialects differ only in this expression:

Results differ from the other dialects on oracle, postgresql.

sqrt

sqrt(value: numeric) → FLOAT

Square root.

Sample query:

// sqrt: square root of freight.
FIND orders o
FETCH o.order_id ASC, sqrt(o.freight) root
LIMIT 20

Generated SQL

duckdb · oracle · snowflake · postgresql · mariadb · trino

-- sqrt: square root of freight.
SELECT
  o.order_id
, sqrt(o.freight) AS root
FROM
 orders o
ORDER BY
  o.order_id ASC
FETCH FIRST 20 ROWS ONLY

The remaining dialects differ only in this expression:

exp

exp(value: numeric) → FLOAT

Exponential — e raised to value.

ln

ln(value: numeric) → FLOAT

Natural logarithm — the logarithm to base e.

Sample query:

// ln: natural logarithm.
FIND orders o
FETCH o.order_id ASC, round(ln(100), 6) natural_log
LIMIT 20

Generated SQL

duckdb · oracle · snowflake · mariadb · trino

-- ln: natural logarithm.
SELECT
  o.order_id
, round(ln(100), 6) AS natural_log
FROM
 orders o
ORDER BY
  o.order_id ASC
FETCH FIRST 20 ROWS ONLY

The remaining dialects differ only in this expression:

mssql

-- ln: natural logarithm.
SELECT
  o.order_id
, round(LOG(100), 6) AS natural_log
FROM
 orders o
ORDER BY
  o.order_id ASC
OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY

log10

log10(value: numeric) → FLOAT

Base-10 logarithm.

Sample query:

// log10: base-10 logarithm.
FIND orders o
FETCH o.order_id ASC, log10(1000) base_ten_log
LIMIT 20

Generated SQL

duckdb · postgresql · mariadb · trino

-- log10: base-10 logarithm.
SELECT
  o.order_id
, log10(1000) AS base_ten_log
FROM
 orders o
ORDER BY
  o.order_id ASC
FETCH FIRST 20 ROWS ONLY

The remaining dialects differ only in this expression:

log

log(base: numeric, value: numeric) → FLOAT

Logarithm of value to the given base: log(2, 8) is 3. The base comes first, as it reads aloud.

Sample query:

// log: logarithm to an explicit base — the base comes first.
// T-SQL takes LOG(value, base), the other way round from every other engine, so this
// pins the value rather than just the rendering: log(2, 8) must be 3 everywhere.
FIND orders o
FETCH o.order_id ASC, log(2, 8) log_base_two
LIMIT 20

Generated SQL

duckdb · oracle · snowflake · postgresql · mariadb · trino

-- log: logarithm to an explicit base — the base comes first.
-- T-SQL takes LOG(value, base), the other way round from every other engine, so this
-- pins the value rather than just the rendering: log(2, 8) must be 3 everywhere.
SELECT
  o.order_id
, log(2, 8) AS log_base_two
FROM
 orders o
ORDER BY
  o.order_id ASC
FETCH FIRST 20 ROWS ONLY

The remaining dialects differ only in this expression:

mssql

-- log: logarithm to an explicit base — the base comes first.
-- T-SQL takes LOG(value, base), the other way round from every other engine, so this
-- pins the value rather than just the rendering: log(2, 8) must be 3 everywhere.
SELECT
  o.order_id
, LOG(8, 2) AS log_base_two
FROM
 orders o
ORDER BY
  o.order_id ASC
OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY

sin

sin(value: numeric) → FLOAT

Trigonometric sin (argument in radians).

Sample query:

// sin: trigonometric sine, argument in radians.
FIND orders o
FETCH o.order_id ASC, round(sin(0), 6) sine_of_zero
LIMIT 20

Generated SQL

duckdb · oracle · snowflake · mariadb · trino

-- sin: trigonometric sine, argument in radians.
SELECT
  o.order_id
, round(sin(0), 6) AS sine_of_zero
FROM
 orders o
ORDER BY
  o.order_id ASC
FETCH FIRST 20 ROWS ONLY

The remaining dialects differ only in this expression:

cos

cos(value: numeric) → FLOAT

Trigonometric cos (argument in radians).

Sample query:

// cos: trigonometric cosine, argument in radians.
FIND orders o
FETCH o.order_id ASC, round(cos(0), 6) cosine_of_zero
LIMIT 20

Generated SQL

duckdb · oracle · snowflake · mariadb · trino

-- cos: trigonometric cosine, argument in radians.
SELECT
  o.order_id
, round(cos(0), 6) AS cosine_of_zero
FROM
 orders o
ORDER BY
  o.order_id ASC
FETCH FIRST 20 ROWS ONLY

The remaining dialects differ only in this expression:

tan

tan(value: numeric) → FLOAT

Trigonometric tan (argument in radians).

Sample query:

// tan: trigonometric tangent, argument in radians.
FIND orders o
FETCH o.order_id ASC, round(tan(0), 6) tangent_of_zero
LIMIT 20

Generated SQL

duckdb · oracle · snowflake · mariadb · trino

-- tan: trigonometric tangent, argument in radians.
SELECT
  o.order_id
, round(tan(0), 6) AS tangent_of_zero
FROM
 orders o
ORDER BY
  o.order_id ASC
FETCH FIRST 20 ROWS ONLY

The remaining dialects differ only in this expression:

asin

asin(value: numeric) → FLOAT

Trigonometric asin (argument in radians).

Sample query:

// asin: inverse sine, result in radians.
FIND orders o
FETCH o.order_id ASC, round(asin(1), 6) arcsine_of_one
LIMIT 20

Generated SQL

duckdb · oracle · snowflake · mariadb · trino

-- asin: inverse sine, result in radians.
SELECT
  o.order_id
, round(asin(1), 6) AS arcsine_of_one
FROM
 orders o
ORDER BY
  o.order_id ASC
FETCH FIRST 20 ROWS ONLY

The remaining dialects differ only in this expression:

acos

acos(value: numeric) → FLOAT

Trigonometric acos (argument in radians).

Sample query:

// acos: inverse cosine, result in radians.
FIND orders o
FETCH o.order_id ASC, round(acos(1), 6) arccosine_of_one
LIMIT 20

Generated SQL

duckdb · oracle · snowflake · mariadb · trino

-- acos: inverse cosine, result in radians.
SELECT
  o.order_id
, round(acos(1), 6) AS arccosine_of_one
FROM
 orders o
ORDER BY
  o.order_id ASC
FETCH FIRST 20 ROWS ONLY

The remaining dialects differ only in this expression:

atan

atan(value: numeric) → FLOAT

Trigonometric atan (argument in radians).

Sample query:

// atan: inverse tangent, result in radians.
FIND orders o
FETCH o.order_id ASC, round(atan(0), 6) arctangent_of_zero
LIMIT 20

Generated SQL

duckdb · oracle · snowflake · mariadb · trino

-- atan: inverse tangent, result in radians.
SELECT
  o.order_id
, round(atan(0), 6) AS arctangent_of_zero
FROM
 orders o
ORDER BY
  o.order_id ASC
FETCH FIRST 20 ROWS ONLY

The remaining dialects differ only in this expression:

pi

pi() → FLOAT

Approximate value of π.

Sample query:

// pi: the mathematical constant pi.
FIND orders o
FETCH o.order_id ASC, pi() pi_value
LIMIT 20

Generated SQL

duckdb · snowflake · postgresql · mariadb · trino

-- pi: the mathematical constant pi.
SELECT
  o.order_id
, pi() AS pi_value
FROM
 orders o
ORDER BY
  o.order_id ASC
FETCH FIRST 20 ROWS ONLY

The remaining dialects differ only in this expression:

random

random() → FLOAT

Random value in the range 0.0 <= x < 1.0.

Sample query:

// random: a random value in [0, 1).
FIND orders o
FETCH random() r

Generated SQL

duckdb · postgresql · trino

-- random: a random value in [0, 1).
SELECT
  random() AS r
FROM
 orders o

The remaining dialects differ only in this expression: