The MySQL ROUND function is used to round a numeric value to a specified number of decimal places. This function is particularly useful when dealing with numerical data and you need to control the precision of your results.
Syntax
Here is the basic syntax of the ROUND function:
ROUND(number, [decimal_places])
number: The numeric value that you want to round.
decimal_places (optional): The number of decimal places to round the number to. If omitted, it defaults to 0.
Examples
Rounding to the nearest integer
SELECT ROUND(15.67); -- Result: 16
Rounding to a specific number of decimal places
SELECT ROUND(123.4567, 2); -- Result: 123.46
Rounding with a negative number of decimal places (rounding to the left of the decimal point)
SELECT ROUND(987.654, -2); -- Result: 1000
Considerations
When rounding to a specific number of decimal places, if the digit in the next decimal place is 5 or higher, the preceding digit is rounded up.
SELECT ROUND(3.145, 2); -- Result: 3.15
If the decimal_places argument is negative, the ROUND() function rounds to the left of the decimal point, resulting in a number with zeros in the decimal places.
SELECT ROUND(12345.6789, -2); -- Result: 12300
The ROUND function can be combined with other SQL functions and clauses to achieve more complex calculations.
SELECT id, ROUND(salary * 1.1) AS increased_salary FROM employees;
In summary, the MySQL ROUND function is a versatile tool for manipulating numeric data by adjusting the precision of values to meet specific requirements. It is particularly useful when dealing with financial data, where precise rounding is often necessary.