SQL ROUND Function

The SQL ROUND function is a SQL Mathematical Function. This SQL Server ROUND Function is used to round the specified numeric expression or an individual number to user-specified length or precision. The syntax of the SQL ServerROUND Function is

SELECT ROUND (Numeric_Expression, length, function)
FROM [Source]

• Numeric_Expression: It can be a number or a valid numerical expression, or approximate numeric data type expect bit type.
• Length: It is the precision to which the Numeric_Expression rounded. Length can be a number or a valid numerical expression of type tinyint, smallint, or int.
  • If the length argument is a positive number, Round function will round the Numeric_Expression to the number of the decimal point specified as length.
  • If the length argument is a negative number, Numeric_Expression rounded to the left side of the decimal point.
• Function: This is an optional parameter, and it must be type int, tinyint, or smallint. If we omit this parameter, or if we assign the default 0, then Numeric_Expression will be rounded. If we specify the function value other than 0 then, Numeric_Expression will be truncated.

SQL ROUND Function Example 1

The ROUND Function returns the rounded numeric value. The following query will show multiple ways to use the ROUND function.

DECLARE @i float
SET @i = 248.6759

SELECT ROUND(@i, 0)AS [Round_Result 1]
SELECT ROUND(@i, 1)AS [Round_Result 2]
SELECT ROUND(@i, 2)AS [Round_Result 3]
SELECT ROUND(@i, 3)AS [Round_Result 4]

-- Calculating Round on Negative Values
SELECT ROUND(@i, -1)AS [Round_Result 5]
SELECT ROUND(@i, -2)AS [Round_Result 6]
SELECT ROUND(@i, -3)AS [Round_Result 7]

--Truncated Round Values
SELECT ROUND(@i, 0, 1)AS [Round_Result 8]
SELECT ROUND(@i, 0, 0)AS [Round_Result 9]

OUTPUT

ANALYSIS

In the below statement, We used the SQL Server ROUND function to find the rounded numeric value of the variable @i. We also assigned a new name to that result as ‘Round_Result 1’ using the ALIAS Column.

SELECT ROUND(@i, 0)AS [Round_Result 1]

In the next statement, We used the ROUND Function with a negative length value.

SELECT ROUND(@i, -1)AS [Round_Result 5]

In the below statement, We used the SQL Round Function to showcase the truncation.

SELECT ROUND(@i, 0, 1)AS [Round_Result 8]
SELECT ROUND(@i, 0, 0)AS [Round_Result 9]

ROUND Function Example 2

In this example, We are going to find the rounded numeric values for all the records present in [List Price] using SQL Server ROUND Function.

SELECT [EnglishProductName]
      ,[Color]
      ,[StandardCost]
      ,[ListPrice]
      ,ROUND([ListPrice], 0) AS [List_Round0] 
      ,ROUND([ListPrice], 2) AS [List_Round2] 
      ,ROUND([ListPrice], -1) AS [List_Round-1] 
      ,ROUND([ListPrice], -2) AS [List_Round-2] 
      ,ROUND([ListPrice], 0, 1) AS [List_Round01] 
      ,[SalesAmount]
FROM [SQL Tutorial].[dbo].[Product Sales]

OUTPUT