MySQL mathematical Functions

In addition to the four basic arithmetic operations addition (+), Subtraction (-), Multiplication (*) and Division (/), MySQL also has the Modulo (%) operator. This calculates the remainder left after division.

select 87 % 9;
+--------+
| 87 % 9 |
+--------+
|      6 |
+--------+
1 row in set (0.00 sec)

MySQL – MOD(x, y)

Displays the remainder of x divided by y, SImilar to the Modulus operator.

select MOD(37, 13);

+-------------+
| MOD(37, 13) |
+-------------+
|          11 |
+-------------+
1 row in set (0.00 sec)

MySQL ABS(x)

Calculates the Absolute value of number x. Thus, if x is negative its positive value is returned.

select ABS(-4.05022);
+---------------+
| ABS(-4.05022) |
+---------------+
|       4.05022 |
+---------------+
1 row in set (0.00 sec)

select ABS(4.05022);
+--------------+
| ABS(4.05022) |
+--------------+
|      4.05022 |
+--------------+
1 row in set (0.00 sec)

SQL SIGN(x)

Returns 1, 0 or -1 when x is positive, zero or negative, respectively.

select SIGN(-34.22);
+--------------+
| SIGN(-34.22) |
+--------------+
|           -1 |
+--------------+
1 row in set (0.00 sec)

select SIGN(54.6);
+------------+
| SIGN(54.6) |
+------------+
|          1 |
+------------+
1 row in set (0.00 sec)

select SIGN(0);
+---------+
| SIGN(0) |
+---------+
|       0 |
+---------+
1 row in set (0.00 sec)

POWER(x,y)

Calculates the value of x raised to the power of y.

select POWER(4,3);
+------------+
| POWER(4,3) |
+------------+
|  64.000000 |
+------------+
1 row in set (0.00 sec)

SQRT(x)

Calculates the square root of x.

select SQRT(3);
+----------+
| SQRT(3)  |
+----------+
| 1.732051 |
+----------+
1 row in set (0.00 sec)

ROUND(x) and ROUND(x,y)

Returns the value of x rounded to the nearest integer. ROUND can also accept an additional argument y that will round x to y decimal places.

select ROUND(14.492);
+---------------+
| ROUND(14.492) |
+---------------+
|            14 |
+---------------+
1 row in set (0.00 sec)

select ROUND(4.5002);
+---------------+
| ROUND(4.5002) |
+---------------+
|             5 |
+---------------+
1 row in set (0.00 sec)

select ROUND(-12.773);
+----------------+
| ROUND(-12.773) |
+----------------+
|            -13 |
+----------------+
1 row in set (0.00 sec)

select ROUND(7.235651, 3);
+--------------------+
| ROUND(7.235651, 3) |
+--------------------+
|              7.236 |
+--------------------+
1 row in set (0.00 sec)

FLOOR(x)

Returns the largest integer that is less than or equal to x.

select FLOOR(23.544);
+---------------+
| FLOOR(23.544) |
+---------------+
|            23 |
+---------------+
1 row in set (0.00 sec)

select FLOOR(-18.4);
+--------------+
| FLOOR(-18.4) |
+--------------+
|          -19 |
+--------------+
1 row in set (0.00 sec)

CEILING(x)

Returns the smallest integer that is greater than or equal to x.

select CEILING(54.22);
+----------------+
| CEILING(54.22) |
+----------------+
|             55 |
+----------------+
1 row in set (0.00 sec)

select CEILING(-62.23);
+-----------------+
| CEILING(-62.23) |
+-----------------+
|             -62 |
+-----------------+
1 row in set (0.00 sec)

TAN(x), SIN(x) and COS(x)

Calculate the trignometic ratios for angle x (measured in radians).

select SIN(0);
+----------+
| SIN(0)   |
+----------+
| 0.000000 |
+----------+
1 row in set (0.00 sec)