Tutorial Gateway

  • C
  • C#
  • Java
  • Python
  • SQL
  • MySQL
  • Js
  • BI Tools
    • Informatica
    • Talend
    • Tableau
    • Power BI
    • SSIS
    • SSRS
    • SSAS
    • MDX
    • R Tutorial
    • Alteryx
    • QlikView
  • More
    • C Programs
    • C++ Programs
    • Go Programs
    • Python Programs
    • Java Programs

SQL ROUND Function

by suresh

The SQL ROUND 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 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 Mathematical ROUND Function returns the rounded numeric value. Few ROUND function examples are

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]
SQL ROUND Function 1

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]

Next, We used the ROUND Mathematical function with a negative length value.

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

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 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]
SQL ROUND Function 2

Placed Under: SQL

  • Install SQL Server
  • Install SQL Management Studio
  • Uninstall Management Studio
  • Install AdventureWorks Database
  • SQL Management Studio Intro
  • Connect SQL with sqlcmd utility
  • SQL Attach Database
  • SQL Detach Database
  • SQL Restore Database
  • Restore Database using BAK
  • SQL Rename Database with Files
  • Get SQL Database Names
  • SQL Create Table
  • SQL Rename Table
  • SQL Alter Table
  • SQL Add Column
  • SQL Rename Column
  • Get SQL Table Names in a DB
  • Find SQL Table Dependencies
  • Rename SQL Table & Column
  • SQL Global & Local Temp Table
  • SQL Table Variable
  • SQL Derived Table
  • SQL DATALENGTH
  • SQL Data Types
  • DML, DDL, DCL & TCL Cmds
  • SQL Query Builder
  • SQL ALIAS
  • SQL SELECT Statement
  • SQL SELECT DISTINCT
  • SQL SELECT INTO Statement
  • SQL INSERT Statement
  • SQL INSERT INTO SELECT
  • SQL BULK INSERT or BCP
  • SQL UPDATE Statement
  • SQL UPDATE from SELECT
  • SQL DELETE Statement
  • SQL TRUNCATE Table
  • SQL CASE Statement
  • SQL MERGE Statement
  • SQL Subquery
  • SQL CTE
  • SQL PIVOT
  • SQL UNPIVOT
  • SQL Clauses Examples
  • SQL TOP Clause
  • SQL WHERE Clause
  • SQL ORDER BY Clause
  • SQL GROUP BY Clause
  • SQL HAVING Clause
  • SQL Primary Key
  • SQL Foreign Key
  • SQL Referential Integrity
  • SQL Check Constraint
  • SQL Unique Constraint
  • SQL Default Constraint
  • SQL Clustered Index
  • SQL Non Clustered Index
  • SQL Filtered Indexes
  • SQL COALESCE Function
  • SQL IS NOT NULL
  • SQL IS NULL Function
  • SQL ISNULL
  • SQL JOINS
  • SQL CROSS JOIN
  • SQL FULL JOIN
  • SQL SELF JOIN
  • SQL Outer Joins
  • SQL Cross Join Vs Inner Join
  • SQL LEFT JOIN
  • SQL RIGHT JOIN
  • SQL AND & OR Operators
  • SQL Arithmetic Operators
  • SQL BETWEEN Operator
  • SQL Comparison Operators
  • SQL LIKE
  • SQL EXCEPT
  • SQL EXISTS Operator
  • SQL NOT EXISTS Operator
  • SQL INTERSECT
  • SQL IN Operator
  • SQL NOT IN Operator
  • SQL UNION
  • SQL UNION ALL
  • SQL IF ELSE
  • SQL ELSE IF
  • SQL WHILE LOOP
  • SQL Nested While Loop
  • SQL BREAK Statement
  • SQL CONTINUE Statement
  • SQL GOTO Statement
  • SQL IIF Function
  • SQL CHOOSE Function
  • SQL Change Data Capture
  • SQL Table Partitioning
  • SQL Table Partition using SSMS
  • SQL TRY CATCH
  • SQL VIEWS
  • SQL User Defined Functions
  • SQL Alter User Defined Functions
  • SQL Stored Procedure Intro
  • Useful System Stored Procedures
  • SQL SELECT Stored Procedure
  • SQL INSERT Stored Procedure
  • SQL UPDATE Stored Procedure
  • Stored Procedure Return Values
  • Stored Procedure Output Params
  • Stored Procedure Input Params
  • Insert SP result into Temp Table
  • SQL Triggers Introduction
  • SQL AFTER INSERT Triggers
  • SQL AFTER UPDATE Triggers
  • SQL AFTER DELETE Triggers
  • SQL INSTEAD OF INSERT
  • SQL INSTEAD OF UPDATE
  • SQL INSTEAD OF DELETE
  • SQL STATIC CURSOR
  • SQL DYNAMIC CURSOR
  • SQL FORWARD_ONLY Cursor
  • SQL FAST_FORWARD CURSOR
  • SQL KEYSET CURSOR
  • SQL TRANSACTIONS
  • SQL Nested Transactions
  • SQL ACID Properties
  • Create SQL Windows Login
  • Create SQL Server Login
  • SQL Server Login Error
  • Create SQL Server Roles
  • SQL Maintenance Plan
  • Backup SQL Database
  • SQL Ranking Functions Intro
  • SQL RANK Function
  • SQL PERCENT_RANK Function
  • SQL DENSE_RANK Function
  • SQL NTILE Function
  • SQL ROW_NUMBER
  • SQL Aggregate Functions
  • SQL Date Functions
  • SQL Mathematical Functions
  • SQL String Functions
  • SQL CAST Function
  • SQL TRY CAST
  • SQL CONVERT
  • SQL TRY CONVERT
  • SQL PARSE Function
  • SQL TRY_PARSE Function
  • SQL Calculate Running Total
  • SQL Find Nth Highest Salary
  • SQL Reverse String
  • SQL FOR XML PATH
  • SQL FOR XML AUTO
  • SQL FOR XML RAW

Copyright © 2021 · All Rights Reserved by Suresh

About Us | Contact Us | Privacy Policy