Numeric Functions in mysql

Numeric Functions

The CEIL() and CEILING() functions, which are synonymous, return the smallest integer that is not less than the specified number the following SELECT statement returns a value of 10:

SELECT CEILING(9.327);     returns a value of 10:

SELECT FLOOR(9.327);       returns a value of  9

MOD() Function

The MOD() function is similar to the percentage (%) arithmetic , its the reminder.

SELECT MOD(22, 7);
In this statement, the MOD() function divides 22 by 7 and then returns the remainder.

SELECT PI();
The PI() function returns a value of 3.141593.

POW() and POWER() Functions

the following SELECT statement raises the number 4 by the power of 2:

SELECT POW(4, 2);
The POW() function returns a value of 16.

ROUND() and TRUNCATE() Functions

To round off a number, you must specify that number as an argument of the function. Optionally, 

you can round off a number to a fractional value by specifying the number of decimal places that you want the returned value to include. For example, the following SELECT statement rounds off a number to two decimal places:

SELECT ROUND(4.27943, 2);
SELECT TRUNCATE(4.27943, 2);

This time, the value 4.27 is returned, rather than 4.28, because the original value is rounded toward zero, which means, for positive numbers, it is rounded down, rather than up.

SQRT() function returns to the square root of a specified number:

SELECT SQRT(36);       The statement returns a value of 6.

SELECT TestID, Amount, MOD(Amount, 10) AS Modulo
FROM Test
ORDER BY TestID;

------------------------------------------------------------------------------------------

SELECT TestID, Amount, POW(Amount, 2) AS Raised2
FROM Test
ORDER BY TestID;