SQL Server ROUND() Function

Summary: in this tutorial, you will learn how to use the SQL Server ROUND() function to return a number rounded to a specified precision.

Introduction to the SQL Server ROUND() function

In SQL Server, the ROUND() function allows you to round a number to a specified precision.

Here’s the syntax of the ROUND() function:

ROUND(number, length [, operation])Code language: SQL (Structured Query Language) (sql)

In this syntax:

1) number

number is a numeric value or a numeric expression you want to round.

2) length

The length specifies the precision that you want to round the number.

The length argument can be a literal number or a numeric expression. The type of the length must be either tinyint, smallint, or int.

The length can be positive or negative. If the length is a positive number, the function rounds number to a precision specified by length.

When the length is a negative number, the function rounds the number on the left side of the decimal point, as specified by length.

3) operation

The operation indicates the type of operation that the ROUND() function will perform.

• If the operation is 0, the ROUND function will round the number.
• If the operation is not zero, the ROUND() function will truncate the number.

The operation parameter is optional and defaults to 0.

The ROUND() function returns a number with the type depending on the type of the input number.

The following table shows the type of the input number and the corresponding return type of the ROUND() function:

Rounding algorithm

The ROUND() function uses the rounding to the nearest digit algorithm. Here are the steps the ROUND() function does when rounding a number:

• First, determine the desired number of decimal places to round as specified by the length argument.
• Second, find the digit immediately to the right of the desired decimal place.
• Third, if that digit is 5 or greater, the function will round up the digit at the desired decimal place. If it is less than 5, the function will leave the digit unchanged.
• Finally, change all digits to the right of the desired decimal place to zero.

SQL Server ROUND() function examples

Let’s take some examples of using the SQL Server ROUND() function.

1) Basic SQL Server ROUND() function example

The following example uses the ROUND() function to round a number to the one with two decimal precisions:

SELECT
  ROUND(10.4567, 2) result;Code language: SQL (Structured Query Language) (sql)

Output:

result
-------
10.4600Code language: SQL (Structured Query Language) (sql)

The result of the round is 10.46000, not 10.46. The reason is that the ROUND() function rounds the number but does not change the type of the number.

In this example, the type of the input number is DEC(6,4). Therefore, the result is a number of the same type which is DEC(6,4).

If you want to obtain the result as a number with two digits after the decimal point, you can round a number first and cast it to a desired number. For example:

SELECT
  CAST(ROUND(10.4567, 2) AS DEC(6, 2)) result;Code language: SQL (Structured Query Language) (sql)

Output:

result
-------
10.46Code language: SQL (Structured Query Language) (sql)

2) Using ROUND() function with positive numbers

The following example illustrates how the ROUND() function rounds the positive numbers:

SELECT
  ROUND(10.4567, 2) x,
  ROUND(10.4519, 2) y;Code language: SQL (Structured Query Language) (sql)

Output:

x       | y
--------+------
10.4600 | 10.4500Code language: SQL (Structured Query Language) (sql)

3) Using the ROUND() function with negative numbers

The following example demonstrates how the ROUND() function works with negative numbers:

SELECT ROUND(-10.4567,2) x,
       ROUND(-10.4519,2) y;Code language: SQL (Structured Query Language) (sql)

Output:

x        | y
---------+--------
-10.4600 | -10.4500Code language: SQL (Structured Query Language) (sql)

4) Using the ROUND() function to truncate a number

The following example uses the ROUND() function to truncate a decimal number to a number with two decimal precisions:

SELECT
  ROUND(10.4567, 2, 1) result;Code language: SQL (Structured Query Language) (sql)

Output:

result
--------
10.4500Code language: SQL (Structured Query Language) (sql)

In this example, the third argument of the ROUND() function is 1, so it performs a truncation instead of rounding. Therefore, the result is 10.4500 instead of 10.4600.

5) Using the ROUND() function with negative length

The following example uses the ROUND() function to round a number on the left side of the decimal point by using a negative length:

SELECT
  ROUND(1234.56, -2) result;Code language: SQL (Structured Query Language) (sql)

Output:

result
-------
1200.00
(1 row)Code language: SQL (Structured Query Language) (sql)

6) Using SQL Serer ROUND() function with table data

We’ll use the production.products table from the sample database for the demonstration:

The following example uses the AVG() function to calculate the average list prices of all products in the products table:

SELECT
  AVG(list_price) avg_list_price
FROM
  production.products;Code language: SQL (Structured Query Language) (sql)

Output:

avg_list_price
--------------
1520.591401Code language: SQL (Structured Query Language) (sql)

The average list price has 6 decimal places.

To make it more readable, you can use the ROUND() function to round the average list price and the CAST() function to cast it to a number with two decimal places:

SELECT
  CAST(ROUND(AVG(list_price), 2) AS DEC(10, 2)) avg_list_price
FROM
  production.products;Code language: SQL (Structured Query Language) (sql)

Output:

avg_list_price
--------------
1520.59Code language: SQL (Structured Query Language) (sql)

Summary

• Use the ROUND() function to return a number rounded to a specified precision.