Mathematical Functions

Supported Mathematical Functions

AVG

AVG(expr,expr,...)

Returns the average of a set of values.

Example AVG usage

SELECT
    AVG(`Replacement Cost`,`id`) AS exprVal 
FROM 
    `films` 

ABS

ABS(expr)

Returns the absolute value of a number.

Example ABS usage

SELECT 
    ABS(`Replacement Cost`) AS exprVal 
FROM 
    `films` 

ACOS

ACOS(expr)

Returns the inverse cosine (arc cosine) of a value.

Example ACOS usage

SELECT 
    ACOS(`Replacement Cost`) AS exprVal 
FROM 
    `films` 

ACOSH

ACOSH(expr)

Returns the inverse hyperbolic cosine (hyperbolic arc cosine) of a value.

Example ACOSH usage

SELECT 
    ACOSH(`Replacement Cost`) AS exprVal
FROM 
    `films`  

ASIN

ASIN(expr)

Returns the inverse sine (arc sine) of a value.

Example ASIN usage

SELECT 
    ASIN(`Replacement Cost`) AS exprVal 
FROM 
    `films`  

ASINH

ASINH(expr)

Returns the inverse hyperbolic sine (hyperbolic arc sine) of a value.

Example ASINH usage

SELECT 
    ASINH(`Replacement Cost`) AS exprVal 
FROM 
    `films`  

ATAN

ATAN(expr)

Returns the inverse tangent (arc tangent) of a value.

Example ATAN usage

SELECT 
    ATAN(`Replacement Cost`) AS exprVal 
FROM 
    `films`

ATAN2

ATAN2(y,x)

Returns the inverse tangent (arc tangent) of y / x, where y and x are the first and second values passed to the expression respectively.

Example ATAN2 usage

SELECT 
    ATAN2(3,4) AS exprVal
FROM 
    `films`  

ATANH

ATANH(expr)

Returns the inverse hyperbolic tangent (hyperbolic arc tangent) of a value.

Example ATANH usage

SELECT 
    ATANH(`Replacement Cost`) AS exprVal 
FROM 
    `films`  

BINARY_SIZE

BINARY_SIZE(expr)

Returns the byte size of the expression.

Example BINARY_SIZE usage

SELECT
    id
    ,BINARY_SIZE(`First Name`) as exprVal 
FROM 
    `customers` 

CEIL

CEIL(expr)

Returns the smallest integer greater than or equal to the specified number.

Example CEIL usage

SELECT 
    CEIL(`Replacement Cost`, 1) AS exprVal 
FROM 
    `films`  

DEGREES_TO_RADIANS

DEGREES_TO_RADIANS(expr)

Converts an input value measured in degrees to radians.

Example DEGREES_TO_RADIANS usage

SELECT 
    DEGREES_TO_RADIANS(300) AS exprVal 
FROM 
    `films` 

DIVIDE

DIVIDE(expr,expr)

Divides one number by another and returns the result.

Example DIVIDE usage

SELECT 
    DIVIDE(`Replacement Cost`,10) AS exprVal 
FROM 
    `films`  

EXP

EXP(expr)

Raises Euler's number (i.e. e ) to the specified exponent and returns the result.

Example EXP usage

SELECT 
    EXP(`Replacement Cost`, 1) As exprVal 
FROM 
    `films` 

FLOOR

FLOOR(expr)

Returns the largest integer less than or equal to the specified number.

Example FLOOR usage

SELECT 
    FLOOR(`Replacement Cost`) AS exprVal 
FROM 
    `films` 

LN

LN(expr)

Calculates the natural logarithm ln (i.e log e) of a number and returns the result as a double.

Example LN usage

SELECT 
    LN(`Replacement Cost`) AS exprVal 
FROM 
    `films` 

LOG

LOG(number,base)

Calculates the log of a number in the specified base and returns the result as a double.

Example LOG usage

SELECT 
    LOG(`Replacement Cost`,10) AS exprVal 
FROM 
    `films` 

LOG10

LOG10(expr)

Calculates the log base 10 of a number and returns the result as a double.

Example LOG10 usage

SELECT 
    LOG10(`Replacement Cost`) AS exprVal 
FROM 
    `films`  

MAX

MAX(expr,expr,...)

Returns the max of a set of numbers.

Example MAX usage

SELECT 
    MAX(`Replacement Cost`,10) AS exprVal 
FROM 
    `films` 

MIN

MIN(xpr,expr,...)

Returns the min of a set of numbers.

Example MIN usage

SELECT 
    MIN(`Replacement Cost`,10) AS exprVal
FROM 
    `films` 

MOD

MOD(expr,expr)

Divides one number by another and returns the remainder.

Example MOD usage

SELECT 
    MOD(`Replacement Cost`,10) AS exprVal 
FROM 
    `films`

MULTIPLY

MULTIPLY(expr,expr,...)

Multiplies numbers together and returns the result.

Example MULTIPLY usage

SELECT 
    MULTIPLY(`Replacement Cost`,10) AS exprVal
FROM
    `films`

POW

POW(expr,exponent)

Raises a number to the specified exponent and returns the result.

Example POW usage

SELECT 
    POW(`Replacement Cost`,10) AS exprVal 
FROM 
    `films` 

RADIANS_TO_DEGREES

RADIANS_TO_DEGREES(expr)

Converts an input value measured in radians to degrees.

Example RADIANS_TO_DEGREES usage

SELECT 
    RADIANS_TO_DEGREES(0.5) AS exprVal 
FROM 
    `films` 

RAND

RAND()

Returns a random float between 0 and 1 each time it is called.

Example RAND usage

SELECT 
    RAND() AS exprVal 
FROM 
    `films`  

ROUND

ROUND(expr,[places])

Rounds a number to a whole integer or to a specified decimal place.

Example ROUND usage

SELECT 
    ROUND(`Replacement Cost`,1) AS exprVal
FROM 
    `films`  

SIN

SIN(expr)

Returns the sine of a value that is measured in radians.

Example SIN usage

SELECT 
    SIN(90) AS exprVal 
FROM 
    `films`  

SINH

SINH(expr)

Returns the hyperbolic sine of a value that is measured in radians.

Example SINH usage

SELECT 
    SINH(90) AS exprVal 
FROM 
    `films`  

SQRT

SQRT(expr)

Calculates the square root of a positive number and returns the result as a double.

Example SQRT usage

SELECT 
    SQRT(`id`) AS exprVal 
FROM 
    `films`  

SUBTRACT

SUBTRACT(expr,expr)

Subtracts two numbers to return the difference, or two dates to return the difference in milliseconds, or a date and a number in milliseconds to return the resulting date.

Example SUBTRACT usage

SELECT 
    SUBTRACT(10,`id`) AS exprVal 
FROM 
    `films` 

SUM

SUM(expr,expr,...)

Sums the values provided in the expression.

Example SUM usage

SELECT 
    SUM(`Replacement Cost`,2,id) AS s 
FROM 
    `films`  

TAN

TAN(expr)

Returns the tangent of a value that is measured in radians.

Example TAN usage

SELECT 
    TAN(90) AS exprVal 
FROM 
    `films`  

TANH

TANH(expr)

Returns the hyperbolic tangent of a value that is measured in radians.

Example TANH usage

SELECT 
    TANH(90) AS exprVal 
FROM 
    `films`  

TRUNC

TRUNC(expr,[places])

Truncates a number to a whole integer or to a specified decimal place.

Example TRUNC usage

SELECT 
    TRUNC(`Replacement Cost`, 1) AS exprVal 
FROM 
    `films`