Tutorial Gateway

SQL ROUND Function

by

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 Server ROUND 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 the 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

SQL ROUND Function 1

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 in SQL Server.

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

In the next statement, We used the ROUND Mathematical 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

SQL ROUND Function 2

Placed Under: SQL