SQL ROUND Function 2

SQL ROUND Function

This SQL function rounds 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: A valid numerical expression or approximate numeric data type except for 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, this SQL Round function will circle the Numeric_Expression to the number of the decimal point specified as length.
    • If the length argument is a negative number, the Numeric_Expression value goes 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 assign the default 0, then Numeric_Expression will remove all decimals. If we specify the function value other than 0, the Numeric_Expression truncates.

SQL ROUND Function Example

This Mathematical Function returns the rounded numeric value. Few other examples of this function are

Used the SQL Server ROUND function to find for the numeric value of the variable @i. We also assigned a new name to that result as ‘Result 2’ using the ALIAS Column in SQL Server.

For the Negative One, We used this Mathematical function with a negative length value.

Within the Trunc 1 and 2, we used this math Function to showcase the truncation.

DECLARE @i float
SET @i = 248.6759

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

-- Calculating for Negative Values
SELECT ROUND(@i, -1)AS [Negative One]
SELECT ROUND(@i, -2)AS [Negative Two]
SELECT ROUND(@i, -3)AS [Negative Three]

--Truncated Values
SELECT ROUND(@i, 0, 1)AS [Trunc 1]
SELECT ROUND(@i, 0, 0)AS [Trunc 2]
SQL ROUND Function 1

ROUND Function Example 2

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

SELECT [EnglishProductName]
      ,[Color]
      ,[StandardCost]
      ,[ListPrice]
      ,ROUND([ListPrice], 2) AS [TWO] 
      ,ROUND([ListPrice], -1) AS [-ONE] 
      ,ROUND([ListPrice], -2) AS [-TW0] 
      ,ROUND([ListPrice], 0, 1) AS [3 ARGS] 
      ,[SalesAmount]
FROM [Product Sales]
SQL ROUND Function 2