ROUND function in SQL Server

The article will give you detailed instructions on how to use the SQL Server ROUND () function with the syntax and specific examples to make it easier to visualize and capture the function better.

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 | 👨 1394 Views
« PREV POST
NEXT POST »