ROUND() in SQL
Rounds a number to a specified number of decimal places or to the nearest integer.
Syntax
Return type
ROUND() Function Example
What is ROUND() in SQL?
The ROUND() function in SQL is used to round a numeric value to a specified number of decimal places or to the nearest integer if no decimal places are provided. It is commonly used in financial calculations, statistical analysis, and data formatting where precision is required. The function takes two arguments: the number to be rounded and the number of decimal places. In SQL Server, an optional third parameter determines whether the value is rounded normally (default) or truncated. ROUND() is widely supported in databases like MySQL, PostgreSQL, and SQL Server, making it essential for ensuring consistent numerical precision in queries.
Parameters:
number: The numeric value to be rounded
decimals: (Optional) Number of decimal places to round to. Default is 0.
Example Use Cases:
-- Round prices to 2 decimal places
-- Round quantities to nearest whole number
-- Round percentages to one decimal place
Notes:
Behavior: Rounds away from zero for values exactly halfway between two numbers (e.g., 2.5 rounds to 3)
Performance Considerations: Basic numeric operation, highly efficient
Version Info: Core numeric function available in all major DBMS
Deprecated/Recommended Alternatives: None
Error Handling:
Error: Returns NULL if input is NULL
Recommendation: Use COALESCE if NULL handling needed
Supported Databases:
DBMS
Function / Syntax
Example
Behavior with NULL
MySQL
ROUND(X,D)
ROUND(123.456, 2)
Returns NULL
PostgreSQL
ROUND(v numeric, s int)
ROUND(123.456, 2)
Returns NULL
SQL Server
ROUND(number, length)
ROUND(123.456, 2)
Returns NULL
SQLite
ROUND(X,Y)
ROUND(123.456, 2)
Returns NULL
BigQuery
ROUND(X[, N])
ROUND(123.456, 2)
Returns NULL
Snowflake
ROUND(number[, precision])
ROUND(123.456, 2)
Returns NULL
Athena
ROUND(X,D)
ROUND(123.456, 2)
Returns NULL
Related Functions:
CEIL()/CEILING()
Rounds a number up to the nearest integer or to a specified decimal place. Always rounds away from zero for positive numbers and toward zero for negative numbers.
FLOOR()
Rounds a number down to the nearest integer or to a specified decimal place. Always rounds toward negative infinity.
CAST/CONVERT()
Converts a value from one data type to another. Both CAST and CONVERT perform the same function with different syntax.
