Back to top
  Back to overview

Math functions

These functions can be used when analysing numeric data and numeric collections.

Function Description
math::abs() Returns the absolute value of a number
math::ceil() Rounds a number up to the next largest integer
math::fixed() Returns a number with the specified number of decimal places
math::floor() Rounds a number down to the next largest integer
math::max() Returns the maximum number in a set of numbers
math::mean() Returns the mean of a set of numbers
math::median() Returns the median of a set of numbers
math::min() Returns the minimum number in a set of numbers
math::product() Returns the product of a set of numbers
math::round() Rounds a number up or down to the nearest integer
math::sqrt() Returns the square root of a number
math::sum() Returns the total sum of a set of numbers

math::abs

The math::abs function returns the absolute value of a number.

math::abs(number) -> number

The following example shows this function, and its output, when used in a select statement:

SELECT * FROM math::abs(13.746189);
13

If the argument is not a number, then it will be cast to a number before the function is run:

SELECT * FROM math::abs("13.746189");
13

math::ceil

The math::ceil function rounds a number up to the next largest integer.

math::ceil(number) -> number

The following example shows this function, and its output, when used in a select statement:

SELECT * FROM math::ceil(13.146572);
14

If the argument is not a number, then it will be cast to a number before the function is run:

SELECT * FROM math::ceil("13.146572");
14

math::fixed

The math::fixed function returns a number with the specified number of decimal places.

math::fixed(number, number) -> number

The following example shows this function, and its output, when used in a select statement:

SELECT * FROM math::fixed(13.146572, 2);
13.15

If the argument is not a number, then it will be cast to a number before the function is run:

SELECT * FROM math::fixed("13.146572", 2);
13.15

math::floor

The math::floor function rounds a number down to the next largest integer.

math::floor(number) -> number

The following example shows this function, and its output, when used in a select statement:

SELECT * FROM math::floor(13.746189);
13

If the argument is not a number, then it will be cast to a number before the function is run:

SELECT * FROM math::floor("13.746189");
13

math::max

The math::max function returns the maximum number in a set of numbers.

math::max(number) -> number

The following example shows this function, and its output, when used in a select statement:

SELECT * FROM math::max([26.164, 13.746189, 23, 16.4, 41.42]);
41.42

If any value in the array is not a number, then it will be cast to a number before the function is run:

SELECT * FROM math::max(["26.164", 13.746189, '23', 16.4, "41.42"]);
41.42

If the argument is not an array, then an EMPTY value will be returned:

SELECT * FROM math::max("some invalid argument");
null

math::mean

The math::mean function returns the mean of a set of numbers.

math::mean(array) -> number

The following example shows this function, and its output, when used in a select statement:

SELECT * FROM math::mean([26.164, 13.746189, 23, 16.4, 41.42]);
24.1460378

If any value in the array is not a number, then it will be cast to a number before the function is run:

SELECT * FROM math::mean(["26.164", 13.746189, '23', 16.4, "41.42"]);
24.1460378

If the argument is not an array, then an EMPTY value will be returned:

SELECT * FROM math::mean("some invalid argument");
null

math::median

The math::median function returns the median of a set of numbers.

math::median(array) -> number

The following example shows this function, and its output, when used in a select statement:

SELECT * FROM math::median([26.164, 13.746189, 23, 16.4, 41.42]);
23

If any value in the array is not a number, then it will be cast to a number before the function is run:

SELECT * FROM math::median(["26.164", 13.746189, '23', 16.4, "41.42"]);
23

If the argument is not an array, then an EMPTY value will be returned:

SELECT * FROM math::median("some invalid argument");
null

math::min

The math::min function returns the minimum number in a set of numbers.

math::min(array) -> number

The following example shows this function, and its output, when used in a select statement:

SELECT * FROM math::min([26.164, 13.746189, 23, 16.4, 41.42]);
13.746189

If any value in the array is not a number, then it will be cast to a number before the function is run:

SELECT * FROM math::min(["26.164", 13.746189, '23', 16.4, "41.42"]);
13.746189

If the argument is not an array, then an EMPTY value will be returned:

SELECT * FROM math::min("some invalid argument");
null

math::product

The math::product function returns the product of a set of numbers.

math::product(array) -> number

The following example shows this function, and its output, when used in a select statement:

SELECT * FROM math::product([26.164, 13.746189, 23, 16.4, 41.42]);
5619119.004884841504

If any value in the array is not a number, then it will be cast to a number before the function is run:

SELECT * FROM math::product(["26.164", 13.746189, '23', 16.4, "41.42"]);
5619119.004884841504

If the argument is not an array, then an EMPTY value will be returned:

SELECT * FROM math::product("some invalid argument");
null

math::round

The math::round function rounds a number up or down to the nearest integer.

math::round(number) -> number

The following example shows this function, and its output, when used in a select statement:

SELECT * FROM math::round(13.53124);
14

If the argument is not a number, then it will be cast to a number before the function is run:

SELECT * FROM math::round("13.53124");
14

math::sqrt

The math::sqrt function returns the square root of a number.

math::sqrt(number) -> number

The following example shows this function, and its output, when used in a select statement:

SELECT * FROM math::sqrt(15);
3.872983346207417

If the argument is not a number, then it will be cast to a number before the function is run:

SELECT * FROM math::sqrt("15");
3.872983346207417

math::sum

The math::sum function returns the total sum of a set of numbers.

math::sum(array) -> number

The following example shows this function, and its output, when used in a select statement:

SELECT * FROM math::sum([26.164, 13.746189, 23, 16.4, 41.42]);
120.730189

If any value in the array is not a number, then it will be cast to a number before the function is run:

SELECT * FROM math::sum(["26.164", 13.746189, '23', 16.4, "41.42"]);
120.730189

If the argument is not an array, then an EMPTY value will be returned:

SELECT * FROM math::sum("some invalid argument");
null