HomeProgramSQL Server

ROUND function in SQL Server

This article will show you in detail how to use the SQL Server ROUND () function with syntax and examples to better visualize and capture functions better.

Describe

The ROUND function in SQL Server is used to round numbers to a certain decimal place.

Syntax

To use the ROUND function in SQL Server, we use the following syntax:

 ROUND(number, decimal, operation)

Parameters :

  1. number: number passed to round
  2. Decimal : The number of decimal places is rounded to. This value must be a positive or negative integer. If this parameter is omitted, the ROUND function will round the number to 0 decimal places.
  3. operation: parameter not required. The operation can be any other numeric value. When it is 0 (or omitted), the ROUND function will round the result into a decimal. If the operation is any value other than 0, the ROUND function will cut the result into a decimal.

Principle of rounding :

When you round the number, the system will check the number in position (decimal + 1):

  1. If the number is greater than 4, the number in the decimal position will add 1. The numbers at the back become 0
  2. If the number is less than 5, the number in the decimal position will remain the same. The numbers on the back become 0

For example we have a decimal number of 423.3241

ROUND function in SQL Server Picture 1

  1. SELECT ROUND (423.3241, -2) has a result of 400.0000
  2. SELECT ROUND (423.3241, -1) has a result of 420.0000
  3. SELECT ROUND (423.3241, 0) has a result of 423.0000
  4. SELECT ROUND (423.3241, 1) has a result of 423.3000
  5. SELECT ROUND (423.3241, 2) has a result of 423.3200
  6. SELECT ROUND (423.3241, 3) has a result of 423.3240
  7. SELECT ROUND (423.3241, 4) has a result of 423.3241

Note :

  1. See also CEILING and FLOOR functions.
  2. ROUND function can be used in later versions of SQL Server: SQL Server 2017, SQL Server 2016, SQL Server 2014, SQL Server 2012, SQL Server 2008 R2, SQL Server 2008, and SQL Server 2005.

For example

Take a look and explore some examples of ROUND functions in SQL Server.

Example 1: Round the numbers

 SELECT ROUND(125.315, 2); 
 Result: 125.320 (kết quả được làm tròn vì tham số thứ 3 bị bỏ qua) 
 
 SELECT ROUND(125.315, 2, 0); 
 Result: 125.320 (kết quả được làm tròn vì tham số thứ 3 là 0) 
 
 SELECT ROUND(125.315, 2, 1); 
 Result: 125.310 (kết quả bị cắt bớt vì tham số thứ 3 khác 0) 
 
 SELECT ROUND(125.315, 1); 
 Result: 125.300 (kết quả được làm tròn vì tham số thứ 3 bị bỏ qua) 
 
 SELECT ROUND(125.315, 0); 
 Result: 125.000 (kết quả được làm tròn vì tham số thứ 3 bị bỏ qua) 
 
 SELECT ROUND(125.315, -1); 
 Result: 130.000 (kết quả được làm tròn vì tham số thứ 3 bị bỏ qua) 
 
 SELECT ROUND(125.315, -2); 
 Result: 100.000 (kết quả được làm tròn vì tham số thứ 3 bị bỏ qua)

Example 2: We have the following data table

ROUND function in SQL Server Picture 2

1. Get the sub-column and rate of the website, rounding the ratio to the 2nd decimal place

 SELECT Chuyenmuccon, ROUND(Tyle, 2) AS Tylemoi 
 FROM Quantrimang; 
 
 Result: 
 Chuyenmuccon Tylemoi 
 SQL Server 74.63 
 Facebook 58.99 
 Python 36.22 
 JavaScript 42.68 
 Google Chrome 94.88 
 Instagram 82.16

2. Get the sub-column and the percentage of Programming on the website, rounding the ratio to the integer part

 SELECT Chuyenmuccon, ROUND(Tyle, 0) AS Tylemoi 
 FROM Quantrimang 
 WHERE Chuyenmuclon="Lap trinh"; 
 
 Result: 
 Chuyenmuccon Tylemoi 
 SQL Server 75 
 Python 36 
 JavaScript 43

Previous article: RAND function in SQL Server

Next lesson: SIGN function in SQL Server

5 ★ | 2 Vote
mssqlround functionsql server number handling functionsql server math function

You should read it

May be interested

  • RIGHT function in SQL ServerRIGHT function in SQL Server
    the article will explore and guide you how to use the right function in sql server to extract some characters from the right side of a given string.
  • FLOOR function in SQL ServerFLOOR function in SQL Server
    the floor function in sql server returns the largest integer value but less than or equal to the transmitted numeric expression.
  • MONTH function in SQL ServerMONTH function in SQL Server
    this article will show you in detail how to use the datetime month () processing function in sql server with specific syntax and examples to better visualize and capture functions.
  • DATEDIFF function in SQL ServerDATEDIFF function in SQL Server
    this article will show you in detail how to use sql server's datetime datediff () function with syntax and specific examples to make it easier to visualize and capture functions.
  • GETUTCDATE function in SQL ServerGETUTCDATE function in SQL Server
    this article will show you in detail how to use datetime getutcdate () function in sql server with specific syntax and examples to better visualize and capture functions.
  • SQL Server YEAR functionSQL Server YEAR function
    sql server's year function returns a 4-digit integer that is the year value in the timestamp passed.
  • NULLIF function in SQL ServerNULLIF function in SQL Server
    this article will show you in detail how to use the nullif function handler in sql server with specific syntax and examples to better visualize and capture functions.
  • COUNT function in SQL ServerCOUNT function in SQL Server
    this article will show you in detail how to use functions that handle count () numbers in sql server with specific syntax and examples to better visualize and capture functions.
  • LEN function in SQL ServerLEN function in SQL Server
    the len function in sql server returns the length of the specified string. it is important that the len function does not include whitespace characters at the end of the string when calculating length.
  • STR function in SQL ServerSTR function in SQL Server
    the article will explore and show you how to use str function to return character data converted from digital data in sql server.

SQL Server