SQL Server ROUND() Function
In SQL Server, ROUND() rounds a numeric expression to a specified precision. It accepts the expression, a length, and an optional function argument. See Microsoft’s ROUND() reference.
Syntax
ROUND(numeric_expression, length[, function])
Parameters:
numeric_expression: the number to be rounded.length: the precision to round to. Positive values count decimal places; negative values round to the left of the decimal point.function: an optional integer. If omitted or set to0,ROUND()rounds the value. Any nonzero value truncates it; the value does not select among multiple rounding modes.
Usage
Use a positive length to round to decimal places and a negative length to round to the left of the decimal point. For example, ROUND(748.58, -1) returns 750.00. The optional third argument truncates instead of rounding:
SELECT ROUND(150.75, 0) AS rounded,
ROUND(150.75, 0, 1) AS truncated;
| rounded | truncated |
|---|---|
| 151.00 | 150.00 |
Examples
Here are two examples of using the ROUND() function:
Example 1
Assuming we have the following Sales table:
| OrderID | Product | UnitPrice | Quantity | Discount |
|---|---|---|---|---|
| 1 | A | 100.00 | 2 | 0.1 |
| 2 | B | 50.00 | 3 | 0.05 |
| 3 | C | 10.00 | 10 | 0.2 |
Now, we want to calculate the total amount of each order and round the result to two decimal places. We can use the following SQL statement:
SELECT OrderID, ROUND((UnitPrice * Quantity * (1 - Discount)), 2) AS TotalAmount
FROM Sales
Running the above SQL statement will result in the following:
| OrderID | TotalAmount |
|---|---|
| 1 | 180.00 |
| 2 | 142.50 |
| 3 | 80.00 |
Example 2
Assuming we want to calculate a student’s average score and round the result to one decimal place. We can use the following SQL statement:
SELECT AVG(Score), ROUND(AVG(Score), 1)
FROM Scores
WHERE Course = 'Math'
Running the above SQL statement will result in the following:
| AVG(Score) | ROUND(AVG(Score), 1) |
|---|---|
| 85.4625 | 85.5 |
Avoid overflow when rounding to the left of the decimal point
Negative length can produce a result that needs more integer digits than the input type can hold. This expression raises Msg 8115 because the literal 748.58 is inferred as decimal(5, 2), which cannot represent 1000.00:
SELECT ROUND(748.58, -3);
Provide enough precision before rounding:
SELECT ROUND(CAST(748.58 AS decimal(6, 2)), -3) AS rounded_value;
The result is 1000.00. See SQL Server Error 8115: Arithmetic Overflow.
Conclusion
ROUND() rounds at the requested positive or negative precision. Its optional third argument selects truncation when nonzero; it does not offer multiple rounding modes.