Tutorial Gateway

SQL ROUND Function

by

The SQL ROUND function is one of the Mathematical Function, which is used to round the specified numeric expression, or an individual number to user specified length or precision.

SQL ROUND Syntax

The basic 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 is to be rounded. Length can be a number or a valid numerical expression of type tinyint, smallint, or int.
    • If the length argument is positive number, Round function will round the Numeric_Expression to the number of decimal point specified as length.
    • If the length argument is 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 SQL Server ROUND Function returns the rounded numeric value. The following query will show multiple ways to use ROUND function.

T-SQL CODE

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

Below lines of code is to declare float variable and assigning the value.

DECLARE @i float
SET @i = 248.6759

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

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

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

SQL 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 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

Thank You for Visiting Our Blog

Placed Under: SQL