MySQL Math Functions

Introduction

There are a number of built-in functions in MySQL, including functions for string, date, and numeric values. The built-in MySQL functions known as MySQL Math Functions refer to numeric type functions and commands that can execute mathematical logic.

Math functions are frequently employed to carry out computations swiftly and precisely in computer programming and data analysis. MySQL Math functions allow programmers to execute calculations without having to write lengthy, complex code, which saves time and lowers the possibility of mistakes.

The MySQL Math functions are numerical functions that are used in SQL queries primarily for mathematical computations and provide numeric values as results. Although these math functions handle numbers, if an error occurs while executing a query, they output NULL.

There are numerous functions offered by MySQL that can be used for a variety of mathematical tasks. Each of the functions is listed in the following table along with a brief description:

ABS() Function

The ABS() method returns the absolute value of the number specified. Regardless of whether a number is positive or negative, its absolute value is defined as its distance from zero on a number line.

Syntax:

The numeric value for which you are looking for the absolute value is denoted by number. The ABS() function returns the same number if the value is positive. It returns the positive equivalent of the number if the value is negative.

Example 1: Query:

Output:

Example 2: Query:

Output:

CEILING() Function

The smallest integer value greater than or equal to a given number is returned by the CEILING() function.

Syntax:

number refers to the numeric value for which you are looking for the lowest integer value, that is either larger than or equal to it.

Example 1: Query:

Output:

Example 2: Query:

Output:

Example 3: Query:

Output:

FLOOR() Function

The greatest integer value that is less than or equal to a number is returned by the FLOOR() function.

Syntax:

In this case, the numeric value number is the one for which you are looking for the highest integer value, which is either less than or equal to itself.

Example 1: Query:

Output:

Example 2: Query:

Output:

Example 3: Query:

Output:

ROUND() Function

To round a value to a given number of decimal places, use the ROUND() function.

Syntax:

The numeric value you want to round is referred to as number, and the number of decimal places is called decimals. The ROUND() function rounds a number to the nearest integer if the decimals argument is absent.

Example 1: Query:

Output:

Example 2: Query:

Output:

Example 3: Query:

Output:

TRUNCATE() Function

To truncate a numeric value to a given number of decimal places, use the TRUNCATE() function.

Syntax:

In this case, number denotes the numeric value you wish to truncate, and decimals denotes the number of decimal places to which the number is truncated. The TRUNCATE() function, in contrast to the ROUND() function, merely eliminates the value after the required number of decimal places instead of rounding them off.

Example 1: Query:

Output:

Example 2: Query:

Output:

POW() Function

In order to raise a number to a certain power, we use the POW() function.

Syntax:

number is the base number that should be raised, and power is the exponent you want to raise number to.

Example 1: Query:

Output:

Example 2: Query:

Output:

SQRT() Function

The SQRT() function can be used to determine a number's square root.

Syntax:

The numeric value for which you are trying to find the square root is denoted by number.

Example 1: Query:

Output:

Example 2: Query:

Output:

RAND() Function

To generate a random number between 0 and 1, use the RAND() function.

Syntax:

Example 1: Query:

Output:

Using Multiple Math Functions in Queries

To do complicated computations, we can combine several MySQL math functions within a single query.

Example 1: We can use the following query to determine the absolute value of -15 and then round it to three decimal places:

Query:

Output:

Explanation:
The above query combines the ABS() and ROUND() MySQL math functions to return a rounded absolute value of the number -15.24356 up to three decimal places.

The parameter sent to the ABS() function is -15.24356, a negative value. The ABS() function outputs 15.24356, which is its absolute value. Then, the absolute value 15.24356 is sent to the ROUND() function and the number of decimal places it should be rounded to is 3. The result of the query is therefore 15.244, which is obtained by rounding 15.24356 to three decimal places.

Example 2: Round the result to two decimal places after calculating the average of a column's absolute values.

Let us assume a table loans that has two columns name and loan.

Table:

Query:

Output:

Explanation: The average of the loan column's absolute values is calculated using the ABS() and AVG() MySQL math functions. The answer is then rounded off to two decimal places using the ROUND() function.

Initially, we calculate the absolute of each value in the loan column which provides the following absolute values: 280, 320, 400, 250, 251, 346. After that, we calculate the average of all the 6 values which would be: (280 + 320 + 400 + 250 + 251 + 346) / 6 = 1847/6 = 307.8333. Finally, we round off this value to 2 decimal places and get the final result of 307.83.

Example 3: Query using a combination of TRUNCATE() and ROUND()

Query:

Output:

Explanation: The number 15.261576 is first truncated to three decimal places, giving the output 15.261. Next, we calculate $PI() * 10$ as 31.4159265359 and round it to three decimal places using the ROUND() function. 31.416 is the outcome of this function call. The final result 46.677 is obtained when the two values 15.261 and 31.416 are added together using the + operator.

See Also

• Aggregate Functions in MySQL
• Date and Time Functions in MySQL