June 24, 2021

The SQL ROUND function rounds a numeric value up to a specified number of decimal places or precision.

Description

The SQL ROUND function helps to handle numeric data according to the requirements. Some other SQL functions for this purpose are CEILING, FLOOR, etc. If we do not want to display complete numeric values, we can truncate or round them using the ROUND function.

The ROUND function is used to round a value to the nearest specified decimal place.  The general format for ROUND is

ROUND(value, number of decimal places)

Thus

SELECT ROUND(5.153745,0)

returns 5.000000 as 5.1 is rounded to 5

SELECT ROUND(5.153745,1)

returns 5.200000 as 5.15 is rounded to 5.2 and

SELECT ROUND(5.153745,2)

returns 5.150000 as 5.153 is rounded to 5.15

In all cases results datatype is the same as the input value.

Let’s look at another example, suppose the sales manager asks for sales order detail information.  You provide him with the following query:

SELECT SalesOrderID,
       SalesOrderDetailID,
       OrderQty,
       UnitPrice,
       UnitPriceDiscount,
       LineTotal
  FROM Sales.SalesOrderDetail

He says, “That’s great, but can you round the line totals to the nearest penny?”

How would go about doing that?  Here is a query you can use with the ROUND function to accomplish that task:

SELECT SalesOrderID,
       SalesOrderDetailID,
       OrderQty,
       UnitPrice,
       UnitPriceDiscount,
       ROUND(LineTotal,2) as LineTotal
  FROM Sales.SalesOrderDetail

Here are the line totals rounded
Using ROUND function on numeric results
Query Results using ROUND

One thing to notice is, though the line total is rounded, the number of decimal place are retained.  The round function returns the same data type as the input value, in this case one with 6 decimal places.

Can you think of another way to satisfy this request?  What about converting data types?

Check out this query

SELECT SalesOrderID,
       SalesOrderDetailID,
       OrderQty,
       UnitPrice,
       UnitPriceDiscount,
       Cast(LineTotal as Decimal(10,2)) as LineTotal
  FROM Sales.SalesOrderDetail

This displays the same results, but since the datatype changed, only two decimal places are displayed.

SQL ROUND Usage Notes

The first argument of the ROUND function is the numeric expression that we want to round. It can be of any numeric data type but not the bit data type.

The length is the second argument of tinyint, smallint, or int data type. The length argument can be positive and negative. For positive value, the numeric expression rounds to the number of decimal places specified by the length.

For negative length value, the ROUND function rounds the numeric expression to the left side of the decimal point equal to the length value.

The third argument of the ROUND function is function. It specifies the operation to perform on the numeric expression. The numeric values round according to the specified length for the default value of 0. In the case of a length value other than 0, the numeric expression truncates. The data type of the function argument is tinyint, smallint, or int.

The ROUND function returns a numeric value. The return data type can be int, bigint, money, decimal, float according to the input data type. If the specified length is negative and longer than the number of digits in the numeric expression, the function returns 0.

Syntax

ROUND (numeric_expression, length, function (optional))

SQL ROUND Examples

We can use the ROUND function for estimations, approximations, and truncations. The following examples demonstrate using the ROUND function on a decimal type:

SELECT ROUND(920.9834, 3) result;
/* Answer */
SELECT ROUND(920.9834, 3) result;

The above query rounds the numeric value to 3 decimal places, and the output is 920.9830.

SELECT ROUND(920.9835, 3) result;
/* Answer */
SELECT ROUND(920.9835, 3) result;

Here, we get a different estimate due to the last decimal digit. The 5 on the last decimal place rounds the value to 920.9840.

The following query rounds on the left side of the decimal as the length is negative.

SELECT ROUND(920.9835, -2) result;
/* Answer */
SELECT ROUND(920.9835, -2) result;

The output is 900.000. But what if we change the second digit to 5?

SELECT ROUND(950.9835, -2) result;
/* Answer */
SELECT ROUND(950.9835, -2) result;

The result is an arithmetic overflow. The round function will try to increase 9 by 1, but we will not get 1000 as an output due to arithmetic overflow. To avoid overflow, we first convert the numeric expression to a bigger decimal value and then apply the ROUND function to it.

SELECT ROUND(CAST (950.9835 AS decimal (6,2)),-2) result;
/* Answer */
SELECT ROUND(CAST (950.9835 AS decimal (6,2)),-2) result;

In the following example, we will see how the operation argument affects the ROUND output.

SELECT ROUND(920.9834, 0, 1) result1; SELECT ROUND(920.9834, 2, 1) result2;
/* Answer */
SELECT ROUND(920.9834, 0, 1) result1;
SELECT ROUND(920.9834, 2, 1) result2; 

The first query truncates the numeric value to zero decimal places. The output is 920.0000. The second query truncates to 2 decimal places, and the output is 920.9800.

Lastly, we will see how to use the ROUND function on column values of a database. We will use the AdventureWorks2019 database for the following query.

SELECT ROUND(StandardCost,2) RoundedCost FROM Production.ProductCostHistory
/* Answer */
SELECT ROUND(StandardCost,2) RoundedCost
FROM Production.ProductCostHistory

The above query returns the standard cost values in the Product Cost History table rounded up to 2 decimal places. The data type of the column Standard Cost is money. For better display and readability, we can use the ROUND function on it, as shown above.

See Also

About the author 

Kris Wenzel

Kris Wenzel has been working with databases over the past 30 years as a developer, analyst, and DBA. He has a BSE in Computer Engineering from the University of Michigan and a MBA from the University of Notre Dame. Kris has written hundreds of blog articles and many online courses. He loves helping others learn SQL.

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

Are YOU READY TO START LEARNING SQL SERVER, BUT DON'T KNOW WHERE TO START?

Nothing is worse than, being excited to learn a new tool but not knowing where to start, wasting time learning the wrong features, and being overwhelmed .

But it doesn't have to be this way.

I'm Putting together a free email course to help you get started learning SQL Server.

Name*
Email*
>