SQL Server ROUND() Function

In SQL Server, the ROUND() function is used to round a number to a specified number of decimal places. The function takes two parameters: the first parameter is the number to be rounded, and the second parameter is the number of decimal places to be retained.

Syntax

Here is the syntax for the ROUND() function:

ROUND(numeric_expression, length[, function])

Parameters:

numeric_expression: the number to be rounded.
length: the number of decimal places to be retained.
function: an optional parameter that specifies the rounding mode. The default value is 0, which means rounding. The values can range from 0 to 4, and their meanings are as follows:
- 0: rounding
- 1: rounding up
- 2: rounding down
- 3: rounding towards zero
- 4: rounding to the nearest even number

Usage

The ROUND() function is commonly used for handling currency and calculation problems. For example, to round a floating-point number to a specific number of decimal places for calculation and comparison. In addition, the function can also be used to represent certain numbers in specific formats, such as percentages and currencies.

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'