Rounds a number to the specified decimal place.
Syntax
DOUBLE|DECIMAL ROUND(DOUBLE|DECIMAL <number>[, BIGINT <decimal_places>])Parameters
| Parameter | Required | Type | Description |
|---|---|---|---|
number | Yes | DOUBLE or DECIMAL | The value to round. If the input is a STRING or BIGINT value, it is implicitly converted to DOUBLE. |
decimal_places | No | BIGINT constant | The decimal place to round to. Defaults to 0 (rounds to the nearest integer). Pass a negative value to round to the left of the decimal point; the return value has no decimal part. If the absolute value of decimal_places is greater than or equal to the number of digits in the integer part of number, the function returns 0.0. |
Return value
The return type matches the input type of number:
Input type of number | Return type |
|---|---|
| DOUBLE | DOUBLE |
| DECIMAL | DECIMAL |
| STRING or BIGINT | DOUBLE |
If
decimal_placesis not a BIGINT constant, an error is returned.If
numberordecimal_placesis NULL, NULL is returned.
Note
When number is DOUBLE, the result may have a precision drift because DOUBLE cannot guarantee precision. For details, see ROUND function precision issues.
Examples
Rounding with different scale values
The following table shows ROUND behavior across the full range of decimal_places values, from negative to zero to positive, including edge cases.
| SQL | Result | Notes |
|---|---|---|
SELECT ROUND(125.315) | 125.0 | decimal_places defaults to 0 |
SELECT ROUND(125.315, 1) | 125.3 | Round to 1 decimal place |
SELECT ROUND(125.315, 2) | 125.32 | Round to 2 decimal places |
SELECT ROUND(125.315, 3) | 125.315 | Precision matches original; value unchanged |
SELECT ROUND(123.345, 4) | 123.345 | Specified precision exceeds original decimal places; value unchanged |
SELECT ROUND(-125.315, 2) | -125.32 | Negative number |
SELECT ROUND(123.345, -2) | 100.0 | Round to hundreds place |
SELECT ROUND(123.345, -4) | 0.0 | Integer part has 3 digits; absolute value of scale (4) exceeds it |
SELECT ROUND(NULL) | NULL | NULL input returns NULL |
Related functions
ROUND is a mathematical function. For other data computation and conversion functions, see Mathematical functions.