In this article, we will discuss all available MySQL Math Functions.

Math ABS() function

MySQL’s abs() function is a Math function. The absolute value of a number is calculated using this function.

Syntax:

Select abs(num);

Output:

11

Math ACOS() function

The arc cosine of X, or the value whose cosine is X, is returned If X does not fall inside the -1 to 1 range, NULL is returned.

mysql> SELECT ACOS(1);

-> 0

mysql> SELECT ACOS(1.0001);

-> NULL

Math SIGN() function

Depending on whether X is negative, zero, or positive, returns the sign of the input as -1, 0, or 1

mysql> SELECT SIGN(-32);

-> -1

Math SIN() function

The sine of X, where X is in radians, is returned

mysql> SELECT SIN(PI());

-> 1.2246063538224e-16

Math SQRT() function

The square root of a nonnegative value X is returned.

mysql> SELECT SQRT(4);

-> 2

Math SUM() function

MySQL’s sum() function is a Math function. This function sums the values of two or more expressions.

select sum(aggregate_expression) from tables [where conditions];

Example:

Select sum(marks) from table2;

Output:

Math TAN() function

MySQL’s tan() function is a Math function. The tangent of an integer is calculated using this function.

mysql> SELECT TAN(PI());

-> -1.2246063538224e-16

Math TRUNCATE() function

The number X, shortened to D decimal places, is returned. The answer has no decimal point or fractional component if D is 0. D can be negative, causing the value X’s D digits left of the decimal point to become 0.

mysql> SELECT TRUNCATE(1.223,1);

-> 1.2

Math ASIN() function

The arc sine of X, the value whose sine is X, is returned. NULL is returned if X does not fall inside the -1 to 1 range.

mysql> SELECT ASIN(0.2);

-> 0.20135792079033

Math ATAN2() function

The arc tangent of the two variables X and Y is returned. It’s like calculating the arc tangent of Y / X, except that the quadrant of the result is determined by the signs of both variables.

mysql> SELECT ATAN(-2,2);

-> -0.78539816339745

Math ATAN() function

The value whose tangent is X is returned as the arc tangent of X.

mysql> SELECT ATAN(2);

-> 1.1071487177941

Math AVG() function

MySQL’s average() function is a Math function. The average value of an expression is calculated with this function.

Example:

select avg(marks) as “Total Marks” from table2;

Output:

Math CEIL() function

CEILING() is the same as CEIL() .

The smallest integer number larger than or equal to X is returned.

mysql> SELECT CEILING(1.23);

-> 2

Math COS() function

The cosine of X, where X is in radians, is returned.

mysql> SELECT COS(PI());

-> -1

Math COT() function

The cotangent of X is returned.

mysql> SELECT COT(12);

-> -1.5726734063977

Math COUNT() function

MySQL’s count() function is a Math function. This function is used to obtain the total count for a specific table column.

Select count(id) from table1;

Output:189

Math DEGREES() function

The parameter X is transformed from radians to degrees and returned.

mysql> SELECT DEGREES(PI());

-> 180

Math DIV() function

MySQL’s div() function is a Math function. This function is used to divide two integers, where n is the number to be divided and m is the number to be divided by.

Select 4 div 2;

Output:

2

Math EXP() function

MySQL’s exp() function is a Math function. This function is used to find enumber, i.e e^number

Example:

Select exp(1);

Output:

2.71828182846

Math FLOOR() function

Returns the greatest integer value that is less than or equal to X

mysql> SELECT FLOOR(1.23), FLOOR(-1.23);

-> 1, -2

Math GREATEST() function

MySQL’s largest() function is a Math function. The greatest number from the list is obtained using this function.

Select greatest(4,5,3,9,0);

Output:

9

Math LEAST() function

MySQL’s least() function is a Math function. The smallest number in the list is obtained using this function.

Select least(4,3,2,7);

Output:

2

Math LN() function

MySQL’s LN() function is a Math function. The natural logarithm of an integer is calculated using this function.

Example:

select LN(5);

Output:

1.609437912

Math LOG10() function

The base-10 logarithm of X is returned. The function returns NULL if X is less than or equal to 0.0E0, and a warning “Invalid parameter for logarithm” is displayed.

Output:

mysql> SELECT LOG10(2);

-> 0.30102999566398

Math LOG() function

This method returns the natural logarithm of X when called with only one parameter. The function returns NULL if X is less than or equal to 0.0E0, and a warning “Invalid parameter for logarithm” is displayed.

Math LOG2() function

The base-2 logarithm of X is returned. The function returns NULL if X is less than or equal to 0.0E0, and a warning “Invalid parameter for logarithm” is displayed.

mysql> SELECT LOG(2);

-> 0.69314718055995

Math MAX() function

The MAX() method in MySQL is used to return the highest value in a set of expression values. When we need to find the largest number, we can utilise this aggregate function.

Example:

mysql> SELECT MAX(income) AS “Maximum Income” FROM emp;

Math MIN() function

In MySQL, the MIN() method is used to return the table’s minimal value from a set of values. It’s an aggregate function that comes in handy when we need to find the smallest number, choose the cheapest product, and so on.

mysql> SELECT MIN(income) AS Minimum_Income FROM employees;

Output:

Math MOD() function

Operation in the modulo mode. The remainder of N divided by M is returned.

mysql> SELECT MOD(234, 10);

-> 4

Math PI() function

The value of is returned (pi). The number of decimal places displayed by default is seven, while MySQL internally uses the full double-precision value.

mysql> SELECT PI();

-> 3.141593

Math POWER() function

POW is a synonym for this term ().

The value of X raised to the power of Y is returned.

mysql> SELECT MIN(income) AS Minimum_Income FROM employees;

Math RADIANS() function

Converts the parameter X from degrees to radians and returns it. (Note that 180 degrees is equal to radians.)

mysql> SELECT RADIANS(90);

-> 1.5707963267949

Math RAND() function

Returns a random floating-point number v between 0 and 1.0. Use the expression FLOOR(i + RAND() * (j I to get a random integer R in the range i=R j). For instance, to get a random integer in the range 7 = R < 12, use the following statement:

mysql> CREATE TABLE t (i INT);

Query OK, 0 rows affected (0.42 sec)

mysql> INSERT INTO t VALUES(1),(2),(3);

Query OK, 3 rows affected (0.00 sec)

Records: 3 Duplicates: 0 Warnings: 0

mysql> SELECT i, RAND() FROM t;

+——+——————+

| i | RAND() |

+——+——————+

| 1 | 0.61914388706828 |

| 2 | 0.93845168309142 |

| 3 | 0.83482678498591 |

+——+——————+

3 rows in set (0.00 sec)

Math ROUND() function

X to D decimal places is added to the argument. The data type of X influences the rounding algorithm. If D is not given, it defaults to 0. D can be negative, making the value X’s D digits to the left of the decimal point 0. Any numbers more than 30 (or -30) are truncated, therefore D has a maximum absolute value of 30.

mysql> SELECT ROUND(-1.23);

-> -1