SQL Round Function Examples

Problem

Numerical values are commonly used to represent information, however, these values often have several unnecessary decimal places for data analysis, presentation, or visualization. How can I round values for data representation and how does rounding work in SQL Server?

In this SQL tutorial, we will learn how to use SQL ROUND() function through various examples to round values with different lengths in a SQL database.

Solution

The SQL ROUND function rounds a numeric value to a specified number of decimal places or the nearest integer. The syntax of the ROUND function is as follows:

ROUND(numeric_expression, length [,function])

Mandatory arguments are:

• numeric_expression: the number (input value) you want to round.
• length: the number of decimal places to round the numeric value. It can be a positive or negative integer.
• A third and optional argument is that the function specifies the rounding method. (0 will round the value and is default behavior, if a non-zero value is used it will truncate the value)

The output data type of the ROUND function depends on the input data type. The following table shows the input and output data types correlation.

Let’s see how the ROUND function works using different use cases.

Round Numeric Value After Decimal Values

Here are two numeric values and we want to round them to the 4^th place after the decimal.

• Numeric Value 1: 4368.788444
• Numeric Value 2: 4368.788851

Let’s use the ROUND function and check the output of the below T-SQL.

SELECT 
   ROUND(4368.788444, 4) as Round1,
   ROUND(4368.788851, 4) as Round2

Since the specified length is 4, the ROUND function looks at the first rounding digit (in this case, the 5th digit). If the digit is less than 5, the final value is rounded off to 4 digits. However, if the corresponding digit is greater than or equal to 5, it increases the length digit (4th digit) by one and rounds off the value. To understand the output, look at the diagram below.

Let’s look at the following example to understand the ROUND functionality.

SELECT 
   ROUND(123.19, 1) as Round1,
   ROUND(123.14, 1) as Round2

In the example above, we specified a length of 1 in the ROUND function. Therefore, the first value, 123.19, with the value 9 (>=5), rounds up to 123.20, while the second value, 123.14, rounds the value down to 123.10 without incrementing the number.

Round Numeric Value Before Decimal Values

Previous examples used positive integers for the length argument, which round the number of decimal positions we specify for the length parameter. The negative number rounds off on the left of the decimal point. Therefore, we must specify a negative length to round the numeric value before the decimal. Let’s look at a few SQL queries.

SELECT 
   ROUND(12345.19, -1) as Round1,
   ROUND(12345.14, -2) as Round2,
   ROUND(12345.14, -3) as Round3

In the first example, we specified the length -1, and since the numeric digit to be rounded is greater or equal to five, it is rounded to 12350.00.

The remaining examples (Round2, Round3) round off the left side of the decimal point value and produce output as 12300.00 and 12000.00.

Let’s look at another example of positive and negative length values together.

SELECT 
   ROUND(.92831, 1) as Round1,
   ROUND(.92831, -1) as Round2,
   ROUND(.92831, 3) as Round3

Here is the output:

• Round1: 0.90000
• Round2: 0.00000 – Since we do not have digits before the decimal, the ROUND function returns 0.00000.
• Round3: 0.92800

SQL Server ROUND Function with Variables

You can use variables to assign input values in the ROUND function to get a desired result. In SQL Server, you declare a variable with @.

The SQL commands below declare a variable @value of the decimal data type and assigns a value of 20.19. Later, you pass this variable as an input to the ROUND function.

DECLARE @value DECIMAL(10, 2);
SET @value = 20.19;
SELECT 
   ROUND(@value, 1) as Round1,
   ROUND(@value, 2) as Round2,
   ROUND(@value, 3) as Round3

Similarly, the ROUND function can use a float data type variable:

DECLARE @value FLOAT(10);
SET @value = 20.19;SELECT 
   ROUND(@value, 1) as Round1,
   ROUND(@value, 2) as Round2,
   ROUND(@value, 3) as Round3

Rounding and Truncate

This section will cover the third and optional argument function in the SQL ROUND() function. First, let's run the following SELECT statement and look at the ROUND() function output with the same input value of 230.75.

SELECT    
   ROUND(230.75, 0) as Rounding,
   ROUND(230.75, 0, 1) as Truncating;

Thither first ROUND function uses two mandatory arguments, i.e., input value and length. It rounds the results and returns 231.00.

The second ROUND function uses the third optional argument, specifying its value as 1. If the ROUND function’s third argument is non-zero, it truncates the result. Therefore, we get the output as 230.00.

Let’s look at another example to clarify the difference between rounding and truncating.

SELECT 
   ROUND(8.5129, 2) 'Rounded (by default)',
   ROUND(8.5129, 0, 0) 'Rounded with default function value',
   ROUND(8.1596, 0, 0) 'Rounded with default function value - Another example,
   ROUND(8.5129, 0, 1) 'Rounded  with explicit function value 1.'

Let’s understand the query outputs:

• Rounded (by default): No optional argument. Therefore, it rounds the input value with length 2. Output: 8.5100.
• Rounded with Default Function Value: Uses the third argument with value 0 (default value). The length argument value is 0. The decimal place value is greater than 5. Therefore, it rounds the input value as 9.0000.
• Rounded with Default Function Value (another example): Uses the third argument with value 0 (default value). The length argument value is 0. The decimal place value is less than 5. Therefore, it rounds the input value as 9.0000.
• Rounded with Explicit Function Value 1: Use the optional parameter, value as 1. Therefore, it truncates the input value as 8.0000.

Avoid Arithmetic Overflow Error in ROUND Function Output

Suppose we want to apply the ROUND function on the value 919.15. The following code uses different lengths -1, -2, -4, and -5 and gets the following output.

SELECT 
   ROUND(919.15, -1) as Round1,
   ROUND(919.15, -2) as Round2,
   ROUND(919.15, -4) as Round4,
   ROUND(919.15, -5) as Round5

The Round 4 and Round 5 values are 0.00 because if a negative value is used and the length is larger than the number of digits before the decimal point, the ROUND function returns 0.

We will get an arithmetic overflow error if we try to use length as -3. The query returns this error because 919.15 defaults to the decimal (5,2) data type. It cannot return the value of 1000.00.

To resolve the error, you can change the data type to the appropriate length using the CAST function, as shown below.

SELECT 
   ROUND(919.15, -1) as Round1,
   ROUND(919.15, -2) as Round2,
   ROUND(CAST (919.15 AS decimal (6,2)),-3)  as Round3,
   ROUND(919.15, -4) as Round4,
   ROUND(919.15, -5) as Round5

Next Steps

• Explore Microsoft docs for ROUND statement documentation.
• Read more T-SQL related tips.
• SQL Server SELECT Statement
• SQL String functions in SQL Server, Oracle and PostgreSQL
• SQL Substring Function Examples with T-SQL, R and Python
• What are the Aggregate Functions in SQL
• SQL Coalesce Function
• SQL Server Date and Time Functions with Examples
• SQL Server Rounding Functions – Round, Ceiling and Floor