The SQL ROUND function is used to round a numeric value to a specified precision. It is a mathematical function that is commonly used in SQL queries to manipulate and format data.
The syntax for the ROUND function is as follows:
ROUND(numeric_expression, length [,function])
numeric_expression is the numeric value that you want to round.
length is the number of decimal places to which you want to round the numeric_expression.
function is an optional argument that specifies the rounding method. It can be one of the following:
0 (or omitted): round up if the fractional part is greater than or equal to 0.5, and round down otherwise (the default).
1: round up to the nearest value.
2: round down to the nearest value.
3: round towards zero (truncate the fractional part).
4: round away from zero (round up or down depending on the sign of the value).
Here are some examples of how the ROUND function can be used:
SELECT ROUND(10.1234, 2); -- Result: 10.12 SELECT ROUND(10.5678, 2); -- Result: 10.57 SELECT ROUND(10.1234, 0); -- Result: 10 SELECT ROUND(10.5, 0); -- Result: 11 SELECT ROUND(-10.5, 0); -- Result: -11
In the first example, the numeric value 10.1234 is rounded to two decimal places, resulting in 10.12. In the second example, the numeric value 10.5678 is rounded to two decimal places, resulting in 10.57.
In the third example, the numeric value 10.1234 is rounded to zero decimal places, resulting in 10. The fourth example demonstrates that if the fractional part of a number is exactly 0.5, the ROUND function will round up to the nearest whole number.
The last example demonstrates that if a negative number has a fractional part that is exactly 0.5, the ROUND function will round down to the nearest whole number, which in this case is -11.
Overall, the SQL ROUND function is a useful tool for manipulating and formatting numeric data in SQL queries.