In this tutorial, we will study about the MySQL
ROUND() and MySQL
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()
- ‘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()
- ‘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() 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,
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,
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;
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);
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);
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);
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;
And our output is,
It is very important not to get confused between
ROUND() does your regular mathematical rounding off a number while
TRUNCATE() just deletes up to those decimal places that you specify.