MySQL 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);Code language: SQL (Structured Query Language) (sql)

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);Code language: SQL (Structured Query Language) (sql)

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);Code language: SQL (Structured Query Language) (sql)

And the output we get is,

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;Code language: SQL (Structured Query Language) (sql)

And we get our output as,

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;Code language: SQL (Structured Query Language) (sql)

And our output will be,

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.

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;Code language: SQL (Structured Query Language) (sql)

And we get our output as,

Examples of MySQL TRUNCATE()

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

SELECT TRUNCATE(15.2356, 2);Code language: SQL (Structured Query Language) (sql)

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() 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);Code language: SQL (Structured Query Language) (sql)

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

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);Code language: SQL (Structured Query Language) (sql)

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() 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;Code language: SQL (Structured Query Language) (sql)

And our output is,

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.