Math Functions In MYSQL

CEIL()

CEIL() returns the smallest integer value not less than the number specified as an argument

Syntax:

CEIL(N);

Where  N is the number

Example:

SELECT CEIL(2.2536); //output 3

SELECT CEIL(-2.2536); //output -2

FLOOR()

FLOOR() returns the largest integer value not greater than a number specified as an argument.

Syntax:

FLOOR(N);

Where  N is the number

 Example:

SELECT FLOOR (2.2536); //output 2

SELECT FLOOR (-2.2536); //output –3

ABS() 

ABS() returns the absolute value of a number.

Syntax:

 ABS(N);

Where  N is the number

Example:

SELECT ABS (6); //output 6

SELECT ABS (-6); //output 6

MOD() 

MOD() returns the remainder of a number divided by another number.This function also works on fractional values and returns the exact remainder.The function returns NULL when the value of the divisor is 0.

Syntax:

MOD(N,M), N % M, N MOD M;

Where N  is dividend and M is divisor

Example:

SELECT MOD(17,5); //output 2

SELECT 17 MOD 5; //output 2

SELECT MOD( 13,0 ); //output is null

SQRT(N)
This function returns the non-negative square root of N

Where N is the number.

Syntax:

SQRT(N);

Example:

Select sqrt(49)  // output is 7

ROUND(N,D)

This function returns N rounded to the nearest integer. If a second argument, D, is supplied, then the function returns N rounded to D decimal places. D must be positive or all digits to the right of the decimal point will be removed

Syntax:

 ROUND(N,D)

Where N is the number and D is the decimal places

Example:

Select round(5.69789); //output is 6

Select round(5.69789,2); // output is 5.69

SIGN(N)

This function returns the sign of N (negative, zero, or positive) as .1, 0, or 1.

Syntax:

SIGN(N)

Where N is the number

Example:

Select sign(4.5) // output is 1

Select sign(-4.5) // output is -1

Select sign(0)  // output is 0

GREATEST(n1,n2,n3,....)

The GREATEST() function returns the greatest value in the set of input parameters (n1, n2, n3, and so on). The following example uses the GREATEST() function to return the largest number from a set of numeric values

Syntax:
GREATEST(n1,n2,n3,....)

Where n1, n2, n3 is the numbers

Example:

Select greatest (5, 78, 78, 89, 90, 8, 6, 2); //output is 90

POWER(X,Y)

These two functions return the value of  X  raised to the power of Y.

Example:

Select power (3, 2); // output is 9(3*3)

Select power (2, 4); // output is 16(2*2*2*2)