SQL ROUND() Function: The Complete Guide

The ROUND() is one of the most used SQL functions that return the round number to 2 decimal places.

SQL ROUND()

SQL ROUND() is a built-in function used for rounding a number to a specific decimal place. Here, the user provides the number as an argument, as well as the length for rounding the number. The function also accepts the optional third argument that allows users to specify whether the number is rounded or truncated.

Syntax

SELECT ROUND (Numeric_expression, length, [operation]);

Parameters

1. Numeric_expression: The numeric expression to be rounded. It should not have a bit data type.
2. Length: The number of decimal places to be rounded off. The value must be a positive or negative integer.
3. Operation: It depicts the type of operation the user wants to perform, i.e., rounding or truncating. If 0 is written (or parameter is omitted), it will round the number up to certain decimal places. If the operation is any other value other than 0, then it will truncate the result up to certain decimal places.

Note

1. If the length is not defined, then it will round the number to 0 decimal places.
2. In the operation part, if 0 is written (or parameter is omitted), it will round the number up to certain decimal places. If the operation is any other value other than 0, then it will truncate the result up to certain decimal places.

Examples

Query 1

Select Round (125.315, 2);

Output

125.320

Explanation

Here, the result is rounded to two decimal places because the 3rd parameter is omitted.

Query 2

Select Round (125.35, 2, 0);

Output

125.320

Explanation

Here, also, the result is rounded to 2 decimal places because ‘0’ is specified in the operation part, which depicts rounding off the number.

Query 3

Select Round (125.315, 2, 1);

Output

125.310

Explanation

Here, the result is truncated to 2 decimal places because ‘1’ is specified in the operation part, which depicts truncating a number.

Query 4

Select Round (125.35, -1, 0);

Output

130.00

Explanation

Here, the result is rounded to one decimal places before the decimal, as the length was specified negative.

See also

SQL SIN()

SQL SQRT()

SQL SIGN()

SQL CEILING()

SQL FLOOR()