MySQL Math Functions You Should Know About

In this post, we will discuss the inbuilt Mathematical Functions in MySQL. These functions are of great help while dealing with numeric data and performing mathematical calculations on them. We will discuss below some of the important MySQL Math Functions with examples.

All MySQL math functions return NULL when they encounter an error. For example, in the case of division by zero, passing invalid type of arguments etc, MySQL math functions will return NULL.

Let us now quickly see MySQL Math functions with examples.

ABS()

ABS() function returns the absolute value of a number. The absolute value of a number is the positive value of the number itself. ABS function expects only one input parameter.

SELECT ABS(2.2), ABS(-4.5), ABS(-0.21);
+----------+-----------+------------+
| ABS(2.2) | ABS(-4.5) | ABS(-0.21) |
+----------+-----------+------------+
|      2.2 |       4.5 |       0.21 |
+----------+-----------+------------+

CEIL()

Ceil function returns the next smallest value greater than or equal to the given number, but not less than the given number. It returns an integer value and expects only one input parameter.

SELECT CEIL(10.45), CEIL(-12.6);
+-------------+-------------+
| ceil(10.45) | ceil(-12.6) |
+-------------+-------------+
|          11 |         -12 |
+-------------+-------------+

FLOOR()

Floor function returns the largest integer value smaller than or equal to the given number, but not greater than the given number. It expects only one input parameter.

SELECT FLOOR(5.56) , FLOOR(-8.12);
+-------------+--------------+
| FLOOR(5.56) | FLOOR(-8.12) |
+-------------+--------------+
|           5 |           -9 |
+-------------+--------------+

ROUND()

Round function, rounds the given number up to specified decimal places. It expects two parameters. one mandatory and one optional as shown below. We round the number to specified DecimalPlaces given in the function.

ROUND(Number);
ROUND(Number,DecimalPlaces);

Round with single parameter(Number).

SELECT ROUND(-4.44), ROUND(9,1), ROUND(30.78);
+--------------+------------+--------------+
| ROUND(-4.44) | ROUND(9,1) | ROUND(30.78) |
+--------------+------------+--------------+
|           -4 |          9 |           31 |
+--------------+------------+--------------+

Round with two parameters: Numbers and Decimal Places

SELECT ROUND(20.8379,3), ROUND(20.8379,2), ROUND(-4.1673,2);
+------------------+------------------+------------------+
| ROUND(20.8379,3) | ROUND(20.8379,2) | ROUND(-4.1673,2) |
+------------------+------------------+------------------+
|           20.838 |            20.84 |            -4.17 |
+------------------+------------------+------------------+

In case of positive numbers if the fractional part is less than .5 then it is rounded towards 0 i.e. rounded down to the previous integer. If fractional part is greater than .5 then it is rounded up to the next integer. In case of negative numbers, if fractional part is less than .5 then the number is rounded up to the next integer and if it is greater than .5 then rounded down to the previous integer.

POW()

POW function returns the value raised to the specified number. Two parameters are given to POW function. First parameter is Number and second is power to which the number is raised.

POW(Number,Power);
SELECT POW(3,3) , POW(10,-2);
+----------+------------+
| POW(3,3) | POW(10,-2) |
+----------+------------+
|       27 |       0.01 |
+----------+------------+

RAND()

Sometimes when we are required to generate random numbers in our scripts or queries, in such cases we use MySQL RAND() function. RAND function returns random floating point values. If we wish to obtain a random number between any two given numbers then we can do so by using below formula:

FLOOR(M + RAND() * (N − M);          // Here M < RandomNUmber < N
SELECT RAND();
+-------------------+
| RAND()            |
+-------------------+
| 0.657067875989776 |
+-------------------+

Now, suppose you want to generate random number between 4 and 10. Then according to the formula, M will be 4 and M-N will be 6.

SELECT FLOOR(4 + RAND()*(6));
+-----------------------+
| FLOOR(4 + RAND()*(6)) |
+-----------------------+
|                     7 |
+-----------------------+

SQRT()

It returns Square root of a non-negative number and expects only one input parameter. If you give a negative number as input then it will return NULL.

SELECT SQRT(81), SQRT(-81), SQRT(21) , SQRT(0.51);
+----------+-----------+------------------+-------------------+
| SQRT(81) | SQRT(-81) | SQRT(21)         | SQRT(0.51)        |
+----------+-----------+------------------+-------------------+
|        9 |      NULL | 4.58257569495584 | 0.714142842854285 |
+----------+-----------+------------------+-------------------+

SIGN()

It returns the sign of the number depending upon if the number if negative, positive or zero. For negative numbers, it returns -1, for positive 1 and 0 for zero. SIGN() function can be used to separate negative and positive numbers in your MySQL query.

SELECT SIGN(-20.99), SIGN(30), SIGN(0);
+--------------+----------+---------+
| SIGN(-20.99) | SIGN(30) | SIGN(0) |
+--------------+----------+---------+
|           -1 |        1 |       0 |
+--------------+----------+---------+

LOG()

LOG() function returns natural logarithm of the input value. It will return NULL if the input is an invalid argument for log function.

SELECT LOG(1), LOG(4), LOG(10), LOG(-2);
+--------+--------------------+-------------------+---------+
| LOG(1) | LOG(4)             | LOG(10)           | LOG(-2) |
+--------+--------------------+-------------------+---------+
|      0 | 1.3862943611198906 | 2.302585092994046 |    NULL |
+--------+--------------------+-------------------+---------+

TRUNCATE()

The truncate function returns the number truncated to the specified decimal places. It expects two mandatory parameters, Number which is to be truncated and Decimal places up to which number is to be truncated.

TRUNCATE(Number, Decimal);

Decimal place can take 3 possible values:

  • Positive: If the decimal value given is positive then the number is kept with that many decimal places. If the given number does not have the specified number of decimal places then 0 is added for all those places.
  • Negative: Decimal value negative will make the digits to the left of the decimal to become zero.
  • Zero: It will return the number with no decimal parts. It will return a whole number.

Examples:

1: Truncate with positive decimal value:

SELECT TRUNCATE(10.55,4), TRUNCATE(33.1,3), TRUNCATE(262.2021,3), TRUNCATE(393.4774, 5);
+-------------------+------------------+----------------------+-----------------------+
| TRUNCATE(10.55,4) | TRUNCATE(33.1,3) | TRUNCATE(262.2021,3) | TRUNCATE(393.4774, 5) |
+-------------------+------------------+----------------------+-----------------------+
|           10.5500 |           33.100 |              262.202 |             393.47740 |
+-------------------+------------------+----------------------+-----------------------+

2: Using Truncate with negative decimal value:

SELECT TRUNCATE(11.79, -1), TRUNCATE(123.90, -2), TRUNCATE(279.23,-3),TRUNCATE(452.23, -4);
+---------------------+----------------------+---------------------+----------------------+
| TRUNCATE(11.79, -1) | TRUNCATE(123.90, -2) | TRUNCATE(279.23,-3) | TRUNCATE(452.23, -4) |
+---------------------+----------------------+---------------------+----------------------+
|                  10 |                  100 |                   0 |                    0 |
+---------------------+----------------------+---------------------+----------------------+

3: Number Truncated with decimal value as zero:

SELECT TRUNCATE(12.546,0), TRUNCATE(15.1,0), TRUNCATE(19,-0);
+--------------------+------------------+-----------------+
| TRUNCATE(12.546,0) | TRUNCATE(15.1,0) | TRUNCATE(19,-0) |
+--------------------+------------------+-----------------+
|                 12 |               15 |              19 |
+--------------------+------------------+-----------------+

MOD()

MySQL MOD function returns the remainder of the division operation between two numbers. It expects two mandatory input parameters, dividend, and divisor. It returns whole values as well as fractional values depending upon the remainder value.

MOD(Dividend, Divisor);
SELECT MOD(10,2), MOD(5,2), MOD(-12,5), MOD(20,4.1), MOD(15,0) ;
+-----------+----------+------------+-------------+-----------+
| MOD(10,2) | MOD(5,2) | MOD(-12,5) | MOD(20,4.1) | MOD(15,0) |
+-----------+----------+------------+-------------+-----------+
|         0 |        1 |         -2 |         3.6 |      NULL |
+-----------+----------+------------+-------------+-----------+

SIN()

The SIN() function in MySQL finds the sin value of the given number. It expects only one input parameter and considers it to be in radians. It returns sine value of the number which is floating point value. Below are some examples showing usage of sin() function.

SELECT SIN(2), SIN(PI()), SIN(30);
+--------------------+------------------------+---------------------+
| SIN(2)             | SIN(PI())              | SIN(30)             |
+--------------------+------------------------+---------------------+
| 0.9092974268256817 | 1.2246467991473532e-16 | -0.9880316240928618 |
+--------------------+------------------------+---------------------+

COS()

COS function returns the cosine value of the input number. Input given to cos() function is considered to be in radians.

SELECT COS(2), COS(PI()), COS(30);
+---------------------+-----------+---------------------+
| COS(2)              | COS(PI()) | COS(30)             |
+---------------------+-----------+---------------------+
| -0.4161468365471424 |        -1 | 0.15425144988758405 |
+---------------------+-----------+---------------------+

TAN()

TAN function returns the tangent value of the given number.

SELECT TAN(30), TAN(PI()), TAN(60);
+--------------------+-------------------------+-------------------+
| TAN(30)            | TAN(PI())               | TAN(60)           |
+--------------------+-------------------------+-------------------+
| -6.405331196646276 | -1.2246467991473532e-16 | 0.320040389379563 |
+--------------------+-------------------------+-------------------+

COT()

COT function returns the cotangent of input number.

SELECT COT(30), COT(PI()), COT(90);
+----------------------+-----------------------+---------------------+
| COT(30)              | COT(PI())             | COT(90)             |
+----------------------+-----------------------+---------------------+
| -0.15611995216165922 | -8.165619676597685e15 | -0.5012027833801532 |
+----------------------+-----------------------+---------------------+

Conclusion

In this post, we learned about various math functions of MySQL. You can learn more about them on the official MySQL documentation. Please leave your valuable feedback in the comments section. Also, learn more about MySQL on Concatly.

Spread the Knowledge

Leave a Reply

Your email address will not be published. Required fields are marked *