ROUND() Function in SQL Server
FREE
Overview
This article is about SQL's ROUND function, syntax, parameters, and return type. It will also cover the features and applications of this function, along with suitable examples to help you understand better.
What is ROUND() Function in SQL?
This function in SQL Server rounds off a given number to a specified number of decimal places. The SQL Query to round off a column to a specified decimal place would be like this -
We use SQL Round, Ceiling, and Floor functions to round the values to the nearest numbers. If we do not want to display decimal numbers in the application front end, that is when round() functions come into use.
It can round off the number according to the nearest number, whether we want to round it off on the decimal place or on the tens, hundreds place, etc.
Syntax
Below is the given syntax of the ROUND() function with the parameters required to run the SQL query and fetch the required result by rounding the number to specific decimal places.
Parameters
This function has three parameters - which has individual meaning as given below:
-
NUMBER: The number which has to be rounded off.
-
DECIMALS: Specified number of decimal places up to which the given number has to be rounded off.
-
OPERATIONS: If its value is 0, it rounds the returned result to the number of the decimal. If the value is other than 0, it truncates the output to the number of decimals that are specified. This is an optional parameter, and the default value of this parameter is always 0.
Return Type
It returns the number after rounding off to the specified places which are mentioned in the parameter.
For example - 36.4358 on rounding off after 2 decimal place would be 36.44.
The above SQL query would return 36.44
Features
-
This function always returns the number after rounding off to the specified decimal places.
-
This function accepts integers value, i.e. positive, negative, and zero-valued numbers.
-
This function also accepts fraction numbers.
-
This function rounds off a specified number to specified decimal places, which is mentioned in the parameter.
-
This function works in SQL Server (starting with 2008), Azure SQL Database, Azure SQL Data Warehouse, Parallel Data Warehouse
Examples
Below are some examples where we are fetching results after applying the ROUND() function in an integer.
- Example 1
Output
- Example 2
Output
- Example 3
Output
- Example 4
Output
- Example 5
Output
The ROUND() function can also be applied to the entire column in a table. Some examples are explained below:
Employee Table:
| Name | Employee Id | Yearly Bonus |
|---|---|---|
| Aman | 691 | 5020.96 |
| Harsh | 703 | 3591.32 |
| Yash | 345 | 7080.10 |
On running the SQL Query
The resulting table would look like this -
| Yearly Bonus |
|---|
| 5021.00 |
| 3591.00 |
| 7080.00 |
Student Table:
| Name | Student Id | Avg Marks |
|---|---|---|
| Punit | 1 | 90.34 |
| Aryan | 2 | 79.67 |
| Sonu | 3 | 70.83 |
On running the SQL Query
The resulting table would look like this -
| Student Id | Avg Marks |
|---|---|
| 1 | 90.3 |
| 2 | 79.7 |
| 3 | 70.8 |
Patient_Details Table:
| Name | Patient Id | Weight |
|---|---|---|
| Sumit | 1 | 83.650 |
| Rohan | 2 | 76.669 |
| Rahul | 3 | 75.332 |
On running the SQL Query:
The resulting table would look like this -
| Name | Patient Id | Weight |
|---|---|---|
| Sumit | 1 | 84.000 |
| Rohan | 2 | 77.000 |
| Rahul | 3 | 75.000 |
Application of ROUND() Function in SQL
This function returns the number after rounding off to the specified places. The ROUND() function can fetch us a rounded result to specified decimal places and round off the entire column in a table using SQL Query.
Conclusions
-
ROUND() function is used to round off a given number to specified decimal places.
-
The name of the function is ROUND, which takes two or three parameters depending on the kind of result we want to fetch.
-
This ROUND() function is kind of similar to the ceiling or floor functions of SQL.
-
If the operational(third) parameter is 0 (or not provided in the function), the ROUND() function will round the result to the number of specified decimal places.
-
If the operation parameter is non-zero, then the ROUND() function will truncate the result to the number of specified decimal places.