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
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:
The above query rounds the numeric value to 3 decimal places, and the output is 920.9830.
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.
The output is 900.000. But what if we change the second digit to 5?
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.
In the following example, we will see how the operation argument affects the ROUND output.
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.
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.
