Open-Source Internship opportunity by OpenGenus for programmers. Apply now.

This article at OpenGenus will demonstrate the numeric/Math function and their roles in mathematical operations with syntax and example using MySQL query.These functions are used for mathematical calculation in Data Analysis and Computer programming.

These Numeric/Math functions are:

ABS()
AVG()
ASIN()
ACOS()
ATAN() /ATAN2()
COUNT()
COS()
COT()
CEIL()/CEILING()
DIV()
DEGREES()
EXP()
FLOOR()
GREATEST()
LOG()
LOG2()
LOG10()
LN()
MOD()
PI()
POW()/POWER()
RAND()
ROUND()
SUM()
SQRT()
SIN()
SIGN()
TAN()
TRUNCATE()

1. ABS()

The MySql ABS() function returns absolute (positive) value of number.

Syntax:
syntax for ABS() function is ABS(n) , where n is number literal. If n is negative number the ABS() function return the positive value and if n is zero or positive it has no effect.

mysql>
SELECT ABS(-23.50);
+-------------+
| ABS(-23.50) |
+-------------+
|       23.50 |
+-------------+
1 row in set  

mysql> SELECT ABS(23.50* (-110));
+--------------------+
| ABS(23.50* (-110)) |
+--------------------+
|            2585.00 |
+--------------------+

Example
Using table for better demonstratioN for Query and output of ABS() function:

mysql> 
 SELECT DISTINCT RECEIVE_AMT ,PAYMENT_AMT, ABS(PAYMENT_AMT - RECEIVE_AMT) FROM CUSTOMER;

+-------------+-------------+--------------------------------+
| RECEIVE_AMT | PAYMENT_AMT | ABS(PAYMENT_AMT - RECEIVE_AMT) |
+-------------+-------------+--------------------------------+
|     5000.00 |     2000.00 |                        3000.00 |
|     7000.00 |     9000.00 |                        2000.00 |
|     7000.00 |     7000.00 |                           0.00 |
|     8000.00 |     7000.00 |                        1000.00 |
|    11000.00 |     7000.00 |                        4000.00 |
|     7000.00 |     6000.00 |                        1000.00 |
|    11000.00 |     9000.00 |                        2000.00 |
|     7000.00 |     3000.00 |                        4000.00 |
|     4000.00 |     5000.00 |                        1000.00 |
|     5000.00 |     7000.00 |                        2000.00 |
|     8000.00 |     3000.00 |                        5000.00 |
|     4000.00 |     3000.00 |                        1000.00 |
|     6000.00 |     7000.00 |                        1000.00 |
|     9000.00 |     7000.00 |                        2000.00 |
|     7000.00 |     4000.00 |                        3000.00 |
+-------------+-------------+--------------------------------+

2. AVG()

The MySQL AVG() function returns the average value of an expression.
Syntax: *SELECT AVG(expression) FROM TABLES [WHERE CONDITIONS] *;
Example:

Query for single Expression

mysql>
SELECT AVG(OPENING_AMT) AS "AVG OPENING_AMT" FROM CUSTOMER WHERE OPENING_AMT >=5000;

+-----------------+
| AVG OPENING_AMT |
+-----------------+
|     6818.181818 |
+-----------------+
1 row in set

Use of AVG() with Distinct clause : You can use distinct clause within AVG() function it returns average column values with unique values in column.

mysql>
SELECT AVG(DISTINCT OPENING_AMT) AS "AVG OPENING_AMT" FROM CUSTOMER WHERE OPENING_AMT >=5000;

+-----------------+
| AVG OPENING_AMT |
+-----------------+
|     7200.000000 |
+-----------------+
1 row in set

Use of AVG() with Group By clause : MySQl AVG() function when used with group by clause it returns average value of expression for each group .

mysql>
SELECT DISTINCT CUST_NAME, AVG(OUTSTANDING_AMT) AS "AVG AMOUNT" FROM CUSTOMER GROUP BY CUST_NAME;
+-------------+--------------+
| CUST_NAME   | AVG AMOUNT   |
+-------------+--------------+
| Micheal     |  6000.000000 |
| Bolt        |  3000.000000 |
| Martin      |  8000.000000 |
| Winston     |  6000.000000 |
| Sasikant    | 11000.000000 |
| Shilton     | 11000.000000 |
| Ramanathan  |  9000.000000 |
| Karolina    |  5000.000000 |
| Ramesh      | 12000.000000 |
| Charles     |  5000.000000 |
| Sundariya   | 11000.000000 |
| Steven      |  3000.000000 |

Use of AVG() with Formula : AVG() function also can be used for more expression which has Multiple-field of expression.

mysql> 
SELECT AVG(OUTSTANDING_AMT*50) AS "AVG_INCREAMENT" FROM CUSTOMER;
+----------------+
| AVG_INCREAMENT |
+----------------+
|  380000.000000 |
+----------------+
1 row in set

3. ASIN()

The MySQL ASIN() function is used to return arc sine of a number in the range of [-1, 1] , It accepts only one parameter. Returns NULL if the number is not in range of [-1,1].

mysql> 
SELECT ASIN(1) AS "ASIN_VALUE";

+--------------------+
| ASIN_VALUE         |
+--------------------+
| 1.5707963267948966 |
+--------------------+
1 row in set 

mysql> 
SELECT DISTINCT ASIN(GRADE) AS "ASIN_GRADE" FROM CUSTOMER;

+--------------------+
| ASIN_GRADE         |
+--------------------+
|               NULL |
| 1.5707963267948966 |
|                  0 |
+--------------------+
3 rows in set

4. ACOS()

The MySQL ACOS() function in MySQL is used to return the arc cosine of number in the range of [ -1, 1].It will Returns NULL if the number is not in the range -1 to 1.


mysql> 
SELECT ACOS(0.75) AS "ACOS_VALUE";

+--------------------+
| ACOS_VALUE         |
+--------------------+
| 0.7227342478134157 |
+--------------------+
1 row in set 

mysql> 
SELECT DISTINCT ACOS(GRADE) AS "ACOS_GRADE" FROM CUSTOMER;

+--------------------+
| ACOS_GRADE         |
+--------------------+
|               NULL |
|                  0 |
| 1.5707963267948966 |
+--------------------+
3 rows in set

5. ATAN()/ATAN2()

The MySQL ATAN() and ATAN2() is used return the arc tangent of any number and also ATAN2(m,n), arc tangent of between m and n values where these values could be positive or negative.

mysql>
SELECT ATAN(-1,2) AS ATAN_VALUE;

+---------------------+
| ATAN_VALUE          |
+---------------------+
| -0.4636476090008061 |
+---------------------+
1 row in set 

mysql> 
SELECT ATAN(PI()) AS ATAN_VALUE;

+--------------------+
| ATAN_VALUE         |
+--------------------+
| 1.2626272556789115 |
+--------------------+
1 row in set

mysql>
SELECT ATAN2(-1,2) AS ATAN_VALUE;

+---------------------+
| ATAN_VALUE          |
+---------------------+
| -0.4636476090008061 |
+---------------------+
1 row in set

6. COUNT()

The MySQL COUNT() function return the count of given expression.
Syntax: syntax for count() function :
SELECT COUNT(expression) FROM TABLES WHERE [condition];

Here "expression" is column name whose No-NULL values is not included in counting.
Count() function can be used within single-expression, Distinct-clause and with Group-by clause.

 mysql>
 SELECT COUNT(CUST_CODE) FROM CUSTOMER;
 
+------------------+
| COUNT(CUST_CODE) |
+------------------+
|               25 |
+------------------+
1 row in set

Use of COUNT() with Where clause-

mysql>
SELECT COUNT(*) AS "NUM_OF_CUST" FROM CUSTOMER WHERE CUST_CITY = 'NEW YORK';

+-------------+
| NUM_OF_CUST |
+-------------+
|           3 |
+-------------+
1 row in set

Use of COUNT() with Group By clause-

mysql>
SELECT CUST_COUNTRY , COUNT(*) AS "NUM_OF_COUNTRY" FROM CUSTOMER WHERE PAYMENT_AMT>= 5000 GROUP BY CUST_COUNTRY;

+--------------+----------------+
| CUST_COUNTRY | NUM_OF_COUNTRY |
+--------------+----------------+
| USA          |              3 |
| Canada       |              3 |
| Australia    |              3 |
| India        |              7 |
| UK           |              4 |
+--------------+----------------+
5 rows in set

7. COS()

MySQL COS() function return the cosine value of given number. Cos() function in MySQL is used to perform mathematical calculation which involve trigonometric functions and Input number should be in radians not in degree.

mysql>
SELECT COS(0);
+--------+
| COS(0) |
+--------+
|      1 |
+--------+
1 row in set  

mysql> 
SELECT COS(PI());

+-----------+
| COS(PI()) |
+-----------+
|        -1 |
+-----------+
1 row in set  

mysql> 
SELECT DISTINCT COS(GRADE) FROM CUSTOMER;
+---------------------+
| COS(GRADE)          |
+---------------------+
| -0.4161468365471424 |
| -0.9899924966004454 |
|  0.5403023058681398 |
|                   1 |
+---------------------+
4 rows in set (

8. COT()

MySQL function COT() returns the cotangent of a number COT(number). here number is used to calculate cotangent.
If number is Zero (0), it will give error that value is out of range or return NULL value.

mysql>
SELECT cot(0);
ERROR 1690 (22003): DOUBLE value is out of range in 'cot(0)'

mysql> 
SELECT cot(-1);
+---------------------+
| cot(-1)             |
+---------------------+
| -0.6420926159343306 |
+---------------------+
1 row in set 

mysql> 
SELECT cot(1);
+--------------------+
| cot(1)             |
+--------------------+
| 0.6420926159343306 |
+--------------------+
1 row in set

9. CEIL() /CEILING()

MySQL function CEIL() returns smallest integer number of given expression. Means it will round up the given number or a Floating number to a nearest value which can be greater or equal to given number/value.

CELL() function is used as a Synonym of CEILING() function.

 mysql> 
 SELECT CEILING(32.65);
+----------------+
| CEILING(32.65) |
+----------------+
|             33 |
+----------------+
1 row in set  

mysql>
SELECT CEILING(-32.65);
+-----------------+
| CEILING(-32.65) |
+-----------------+
|             -32 |
+-----------------+
1 row in set  

mysql> 
SELECT CEILING(-32.12);
+-----------------+
| CEILING(-32.12) |
+-----------------+
|             -32 |
+-----------------+
1 row in set

10. DIV()

MySQL function DIV() return integer value (Quotient) when integer is done , means m is divided by n.
Syntax SELECT m DIV n;
In above syntax m is value that wil be divided by n.

DIV() function also can be used with BIGINIT values , BIGINIT is the Data-type in MySQL and takes 8 bytes of memory and can store values in range of -2^63 to 2^63-1 .


mysql>
SELECT 9.5 DIV 3.5;

+-------------+
| 9.5 DIV 3.5 |
+-------------+
|           2 |
+-------------+
1 row in set  

mysql> 
SELECT 232313  DIV 231 AS "RESULT";

+--------+
| RESULT |
+--------+
|   1005 |
+--------+
1 row in set

11. DEGREES()

This DEGREES() MySQL function converts the radian value into degrees.
Syntax : SELECT DEGREES(1.5);

mysql> 
SELECT DEGREES (1.5);

+-------------------+
| DEGREES (1.5)     |
+-------------------+
| 85.94366926962348 |
+-------------------+
1 row in set  

mysql> 
SELECT DEGREES (PI());
+----------------+
| DEGREES (PI()) |
+----------------+
|            180 |
+----------------+
1 row in set

12. EXP()

MySQL function EXP() return the value of e^number , where number is specified value. 'e' is the mathematical constant which is base of natural logarithms.

Logarithm value of e is equal to 1.
Value of e is approximately = 2.71828.

 mysql>
 SELECT EXP(1);
 
+-------------------+
| EXP(1)            |
+-------------------+
| 2.718281828459045 |
+-------------------+
1 row in set  

mysql> 
SELECT EXP(0);
+--------+
| EXP(0) |
+--------+
|      1 |
+--------+
1 row in set  

mysql>
SELECT EXP(30);
+--------------------+
| EXP(30)            |
+--------------------+
| 10686474581524.463 |
+--------------------+
1 row in set  

mysql>
SELECT EXP(-1);

+---------------------+
| EXP(-1)             |
+---------------------+
| 0.36787944117144233 |
+---------------------+
1 row in set

13. FLOOR()

MySQL function FLOOR() return the largest integer values which is less than or equal to expression value.

FLOOR() function also round given number in a nearest integer value.


mysql>
SELECT FLOOR(123.921);
+----------------+
| FLOOR(123.921) |
+----------------+
|            123 |
+----------------+
1 row in set  

mysql>
SELECT FLOOR(-123.921);
+-----------------+
| FLOOR(-123.921) |
+-----------------+
|            -124 |
+-----------------+
1 row in set

14. GREATEST()

MySQL function GREATEST() return the largest number in a given expression or in a column . It accepts multiple arguments and returns one of the largest value. GREATEST() function doesn't accepts DISTINCT() keyword compare to MAX() function.

mysql>
SELECT GREATEST('customer' , 'cust_name', 'addres' , 'DEPARTMENT') AS "GREEATEST" ;

+------------+
| GREATEST  |
+------------+
| DEPARTMENT |  
+------------+
1 row in set 

mysql>
SELECT GREATEST(10,23,44.55,65,76,32,98,12,33) AS "GREATEST_VLUE" ;

+---------------+
| GREATEST_VLUE |
+---------------+
|         98.00 |
+---------------+
1 row in set

15. LOG()

LOG(): This MySQL function returns the logarithm of a number.it return natural logarithm for one specified value or parameter.

If value of two parameter or specified number value is provided than it return LOG with the base of specified number.

Base of natural logarithms is mathematical constant 'e' which have the value of 2.71828... .

syntax 1. SELECT LOG(NUMBER);
2. * SELECT LOG(BASE, NUMBER);*
Number to get natural logarithm must be greater than 0 and Base must greater than 1.


mysql> 
SELECT LOG(10);

+-------------------+
| LOG(10)           |
+-------------------+
| 2.302585092994046 |
+-------------------+
1 row in set 

mysql> 
SELECT LOG(1);

+--------+
| LOG(1) |
+--------+
|      0 |
+--------+
1 row in set

16. LOG2()

MySQL function LOG2() returns the base 2 logarithm of number , So base must be greater than zero.


mysql>
SELECT LOG2(10);
+-------------------+
| LOG2(10)          |
+-------------------+
| 3.321928094887362 |
+-------------------+
1 row in set 

mysql>
SELECT LOG2(1);
+---------+
| LOG2(1) |
+---------+
|       0 |
+---------+
1 row in set

SELECT LOG(2,1);
+----------+
| LOG(2,1) |
+----------+
|        0 |
+----------+
1 row in set  

mysql> 
SELECT LOG(2,5);
+-------------------+
| LOG(2,5)          |
+-------------------+
| 2.321928094887362 |
+-------------------+
1 row in set

17. LOG10()

This MySQl function LOG10() returns the number of base 10 logarithm.The number will return NULL if number is less than 0.


mysql> 
SELECT LOG10(10);
+-----------+
| LOG10(10) |
+-----------+
|         1 |
+-----------+
1 row in set  

mysql> SELECT LOG10(1);
+----------+
| LOG10(1) |
+----------+
|        0 |
+----------+
1 row in set  

mysql> SELECT LOG10(5);
+--------------------+
| LOG10(5)           |
+--------------------+
| 0.6989700043360189 |
+--------------------+
1 row in set

18. LN()

This MySQL function returns only natural logarithm of number, and number must be greater than 0.

mysql>
SELECT LN(10);
+-------------------+
| LN(10)            |
+-------------------+
| 2.302585092994046 |
+-------------------+
1 row in set  

mysql> 
SELECT LN(1);
+-------+
| LN(1) |
+-------+
|     0 |
+-------+
1 row in set  

mysql>
SELECT LN(4);
+--------------------+
| LN(4)              |
+--------------------+
| 1.3862943611198906 |
+--------------------+
1 row in set

19. MOD()

This MySQL function MOD() return the remainder when two number x divided by y. MOD() also works on values that have a fractional part and returns the exact remainder after division.

Syntax: SELECT MOD(X,Y);

SELECT x MOD y;* or it also can be used as-
SELECT x % y ;


mysql> 
SELECT 14 MOD 5;
+----------+
| 14 MOD 5 |
+----------+
|        4 |
+----------+
1 row in set 

mysql> 
SELECT MOD(10,3);
+-----------+
| MOD(10,3) |
+-----------+
|         1 |
+-----------+
1 row in set  

mysql> SELECT 20 % 11;
+---------+
| 20 % 11 |
+---------+
|       9 |
+---------+
1 row in set

20. PI()

The MySQl function PI() returns the value of π (pi), by default the value is displayed in seven decimal places.
MySQL uses the full double-precision value and it uses 8 bytes to store .
By using Precision specification we can get the value of PI π to required decimal places. .

mysql> 
SELECT PI();

+----------+
| PI()     |
+----------+
| 3.141593 |
+----------+
1 row in set  

mysql> 
SELECT PI()*1.000000000000000000000;

+------------------------------+
| PI()*1.000000000000000000000 |
+------------------------------+
|      3.141592653589793000000 |
+------------------------------+
1 row in set

21. POW()/POWER()

This function POW() returns x^n : x raised to the power of n . where x is the base and n is the exponent in calculation. If x or y is the zero (0) then it returns NULL.
The POWER() function is the synonym of POW().

Syntax: SELECT POWER(x,n); or SELECT POW(x,n);

mysql>
SELECT POWER(2,10);
+-------------+
| POWER(2,10) |
+-------------+
|        1024 |
+-------------+
1 row in set  

mysql> 
SELECT POWER(3,0.5);

+--------------------+
| POWER(3,0.5)       |
+--------------------+
| 1.7320508075688772 |
+--------------------+
1 row in set

22. RAND()

This function in MySQL is used to generate random number and random number within a range .

Syntax: SELECT RAND([seed])
SEED - It is value which is optional, If value is specified each time it generates repeatable sequence of number .Or It will generate completely random number if no seed value is provided.

mysql> 
SELECT GRADE , RAND() FROM CUSTOMER;
+-------+----------------------+
| GRADE | RAND()               |
+-------+----------------------+
|     2 |   0.5770212352061842 |
|     3 |    0.635253416034629 |
|     2 |  0.44520340528823754 |
|     1 |  0.32025680907094556 |
|     1 |  0.26567386022366013 |
|     1 |  0.36759890463910894 |
|     1 |  0.04097297325820939 |
|     1 |   0.1020681831073651 |
|     3 |   0.3874220264958423 |
|     3 |   0.6309056138907612 |
|     0 |   0.5866155750924866 |
|     2 |  0.13749940985580852 |
|     2 |   0.9276503547352276 |
+-------+----------------------+
25 rows in set

The RAND() function will provide the random value in the range of [x,y] where x will be inclusive and y will be exclusive. so random number will be value>=x and <y.
To retrieve rows in random order from table , we can use RAND() function with oreder by clause like this:
Syntax: SELECT FROM table_name ORDER BY RAND() LIMIT 12;

mysql>
SELECT DISTINCT WORKING_AREA FROM CUSTOMER ORDER BY RAND();
+--------------+
| WORKING_AREA |
+--------------+
| San Jose     |
| Torento      |
| Hampshair    |
| Brisban      |
| New York     |
| London       |
| Bangalore    |
| Chennai      |
| Mumbai       |
+--------------+
9 rows in set

Generating Random Decimal: To generate random Decimal number in MySQL in a range of two number :
Syntax: SELECT RAND()*(y-x)+x; where y is greater number and x is smaller.

mysql>
SELECT RAND()*(15-9)+9;
+--------------------+
| RAND()*(15-9)+9    |
+--------------------+
| 14.389509602812595 |
+--------------------+
1 row in set 

mysql> 
SELECT RAND()*(15-9)+9;
+------------------+
| RAND()*(15-9)+9  |
+------------------+
| 9.10952900546559 |
+------------------+
1 row in set

Generating Random Integer: To generate random Integer in MySQL between range of two values, for inclusive values we add +1 with the value.
Syntax SELECT FLOOR(RAND()*(y-x+1)+x);

RAND_Q4

23. ROUND()

ROUND(), MySQL /SQL function returns the rounded decimal value of Number to certain places.
Syntax SELECT ROUND(number,decimal_digit);

Note: Here 'decimal_digit' can be negative because it round the number to left side after decimal and if 'decimal_digit' is positive it will round the number to right side after decimal

mysql>
SELECT FLOOR (RAND()*(15-9+1)+9);
+---------------------------+
| FLOOR (RAND()*(15-9+1)+9) |
+---------------------------+
|                        11 |
+---------------------------+
1 row in set  

mysql>
SELECT FLOOR (RAND()*(15-9+1)+9);
+---------------------------+
| FLOOR (RAND()*(15-9+1)+9) |
+---------------------------+
|                        15 |
+---------------------------+
1 row in set 

mysql>
SELECT FLOOR (RAND(6)*(15-9+1)+9);
+----------------------------+
| FLOOR (RAND(6)*(15-9+1)+9) |
+----------------------------+
|                         13 |
+----------------------------+
1 row in set  

mysql>
SELECT FLOOR (RAND(6)*(15-9+1)+9);
+----------------------------+
| FLOOR (RAND(6)*(15-9+1)+9) |
+----------------------------+
|                         13 |
+----------------------------+
1 row in set

24. SUM()

MySQL function SUM() returns the sum of values of given expressions.

Syntax: SELECT SUM(expression)
FROM table [WHERE conditions];
When putting the expression after 'where' clause you must include Group By clause.

mysql> 
SELECT SUM(RECEIVE_AMT ) AS "TOTAL_AMT" FROM CUSTOMER WHERE RECEIVE_AMT<=7000;
+-----------+
| TOTAL_AMT |
+-----------+
| 101000.00 |
+-----------+
1 row in set  

mysql>
SELECT SUM( DISTINCT RECEIVE_AMT ) AS "TOTAL_AMT" FROM CUSTOMER WHERE RECEIVE_AMT>=5000;
+-----------+
| TOTAL_AMT |
+-----------+
|  46000.00 |
+-----------+
1 row in set

Example of SUM() Function with group by clause :

mysql>
SELECT CUST_NAME,  SUM(RECEIVE_AMT ) AS "TOTAL_AMT" FROM CUSTOMER GROUP BY CUST_NAME;

+-------------+-----------+
| CUST_NAME   | TOTAL_AMT |
+-------------+-----------+
| Micheal     |   5000.00 |
| Bolt        |   7000.00 |
| Martin      |   7000.00 |
| Winston     |   8000.00 |
| Sasikant    |  11000.00 |
| Shilton     |   7000.00 |
| Ramanathan  |  11000.00 |
| Karolina    |   7000.00 |
| Ramesh      |   7000.00 |
| Charles     |   4000.00 |

25. SIGN()

This MySQL function returns the sign value which indicates the sign of th number.

If returned value is O then Number is equal to 0
If returned value is 1 then Number is greater than 1 and positive.
If returned value is -1 then Number is less than 0 and negative.

mysql>
SELECT SIGN(12);
+----------+
| SIGN(12) |
+----------+
|        1 |
+----------+
1 row in set  

mysql> 
SELECT SIGN(PI());
+------------+
| SIGN(PI()) |
+------------+
|          1 |
+------------+
1 row in set  

mysql>
SELECT SIGN(-1123333);
+----------------+
| SIGN(-1123333) |
+----------------+
|             -1 |
+----------------+
1 row in set

26. SIN()

This MySQL function Returns the sine of value, where value is given in radians. Returns NULL if value is NULL.

 mysql>
 SELECT SIN(90);
+--------------------+
| SIN(90)            |
+--------------------+
| 0.8939966636005579 |
+--------------------+
1 row in set  

mysql> 
SELECT SIN(0);
+--------+
| SIN(0) |
+--------+
|      0 |
+--------+
1 row in set 

mysql>
SELECT SIN(PI());
+------------------------+
| SIN(PI())              |
+------------------------+
| 1.2246467991473532e-16 |
+------------------------+
1 row in set

27. SQRT()

This SQRT() function of MySQL returns the square root of a nonnegative number like a. If a is negative, the function returns NULL.

mysql> 
SELECT SQRT(2);
+--------------------+
| SQRT(2)            |
+--------------------+
| 1.4142135623730951 |
+--------------------+
1 row in set  

mysql>
SELECT SQRT(-10);
+-----------+
| SQRT(-10) |
+-----------+
|      NULL |
+-----------+
1 row in set  

mysql>
SELECT SQRT(PI());
+--------------------+
| SQRT(PI())         |
+--------------------+
| 1.7724538509055159 |
+--------------------+
1 row in set

28. TAN()

AN() MySQL function returns the tangent of a number , where number's value is in radian.
Syntax: SELECT TAN(number);

mysql> 
SELECT TAN(PI());
+-------------------------+
| TAN(PI())               |
+-------------------------+
| -1.2246467991473532e-16 |
+-------------------------+
1 row in set  

mysql>
SELECT TAN(90);
+--------------------+
| TAN(90)            |
+--------------------+
| -1.995200412208242 |
+--------------------+
1 row in set  

mysql> 
SELECT TAN(1);
+--------------------+
| TAN(1)             |
+--------------------+
| 1.5574077246549023 |
+--------------------+
1 row in set

29. TRUNCATE()

This MYSQL function TRUNCATE() returns the number truncated to a certain places of decimals, which is specified in expression.

Syntax: SELECT TRUNCATE(number , decimal_places);

The number for decimal_places should be a positive or negative integer , which truncate the value after the decimal places.
if decimal_number value is 0, than the result has no decimal point or fractional part.


mysql>
SELECT TRUNCATE(215.213, 0);
+----------------------+
| TRUNCATE(215.213, 0) |
+----------------------+
|                  215 |
+----------------------+
1 row in set 

mysql> 
SELECT TRUNCATE(215.21323, 2);
+------------------------+
| TRUNCATE(215.21323, 2) |
+------------------------+
|                 215.21 |
+------------------------+
1 row in set

CONCLUSION

These were the Mathematical function which I have elaborated above and simplify them into smaller parts. In MYSQL and PL/SQL Math functions are mostly used for the calculation purpose. These Math functions are frequently used for computations, also precisely in computer programming and data analysis. MySQL Math functions allow programmers to execute calculations with lowers possibility of mistakes.

Math Functions in SQL/MySQL

MySQL