MySQL ROUND() and TRUNCATE()

ROUND And TRUNCATE

In this tutorial, we will study about the MySQL ROUND() and MySQL TRUNCATE() functions.

You must have studied rounding off to a certain decimal place while studying high school maths. Since it is such a common mathematical operation, MySQL provides us with the ROUND() function. The MySQL ROUND() function is used to round off a number to a given number of decimal places if mentioned.

MySQL also provides us with the TRUNCATE() function. It is used to truncate a number to the specified number of decimal places. For instance, if we have a number say 15.236 and we want to truncate to only two decimal places, then TRUNCATE() would return 15.23. It is important to note that TRUNCATE() does not round off a number, it simply removes those decimal places which are not needed by the user.


Syntax of MySQL ROUND()

ROUND(number, decimal_places);

Where

  • ‘number’ is the number that is to be rounded off and,
  • ‘decimal_places’ is an optional parameter that specifies the number of decimal places to round ‘number’ to.

Syntax of MySQL TRUNCATE()

TRUNCATE(number, decimal_places);

Where

  • ‘number’ is the number to be truncated and,
  • ‘Decimal_places’ is the decimal places to truncate to.

Examples of MySQL ROUND()

Let us round off the following two numbers: 135.2 and 135.8. We do so using the below query,

SELECT ROUND(135.2); 
SELECT ROUND(135.8);

And the output we get is,

MySQL Round Basic Examples

MySQL ROUND() When Decimal Places Are Specified

Let us take a look at two more examples to familiarize ourselves with the function. This time, we will include the number of decimal places we need our number to round off to. Consider the two below queries. We use aliases with our SELECT statement to make our output readable.

SELECT ROUND(25.36251498, 3) AS RoundedOff; 
SELECT ROUND(25.36251498, 1) AS RoundedOff;

And we get our output as,

Round Number 1

What if we mention zero as the number of decimal places? Let’s take a look at such an example.

SELECT ROUND(25.36251498, 0) AS RoundedOff;

And our output will be,

Round Number 2

Since we don’t want any decimal point, we get a whole number (after rounding off) as our result.

ROUND() With Tables

Let us see how ROUND() works with tables. Consider the below Employee table.

Round Employee Table
Employee Table

How about we find the approximate average salary of all the employees? The Approximate average salary is the average salary rounded off to the nearest whole number. We will use the AVG() function. The query for it is,

SELECT ROUND(AVG(Salary)) AS Approx_Average_Salary FROM Employee;

And we get our output as,

Round Table Example

Examples of MySQL TRUNCATE()

Let us look at a basic example of TRUNCATE(). Consider the below query,

SELECT TRUNCATE(15.2356, 2);

TRUNCATE() will essentially just remove the last two decimal places and keep only the first two decimal places and return 15.23 as our result. We can see the output below.

Truncate Basic Example

TRUNCATE() With Zero Number of Decimal Places

What if we pass zero in the decimal places parameter? Consider the below query,

SELECT TRUNCATE(15.2356, 0);

We do not want any decimal places so TRUNCATE() just returns the whole number 15 as shown below.

Truncate Zero

TRUNCATE() With Negative Number of Places

How about passing a negative number in the decimal places parameter? Can you guess what the output would be? Let us consider the below query,

SELECT TRUNCATE(154.123, -2);

TRUNCATE() will turn the first two digits left of the decimal point to zero and remove the numbers right of the decimal point altogether. We can see it in the below output.

Truncate Negative

TRUNCATE() With Tables

Let us consider the same Employee table. This time, let’s just display the approximate average salary of all employees to be the average value truncated to two decimal places.

SELECT TRUNCATE(AVG(Salary), 2) AS Approx_Average_Salary FROM Employee;

And our output is,

MySQL Truncate Table Example

Conclusion

It is very important not to get confused between TRUNCATE() and ROUND(). ROUND() does your regular mathematical rounding off a number while TRUNCATE() just deletes up to those decimal places that you specify.