ROUND()

All functions > MATH > ROUND()

Rounds a number to the nearest integer or to a specified number of decimal places.

Signatures

Decimal

Returns: Literal decimal_places n ≥ 0 → s = n; omitted or literal n < 0 → s = 0; non-literal decimal_places → s = y (input scale)

ROUND(number: DECIMAL(p, y), [decimal_places: BIGINT]) → DECIMAL(p, s)

Signature notes:

• For DECIMAL(p, y), inferred result type is DECIMAL(p, n) when decimal_places is the literal n
• When decimal_places is omitted, the result is DECIMAL(p, 0)
• Non-literal decimal_places keeps the input scale y

Floating-point

Returns: Same type as the input

ROUND(number: T, [decimal_places: BIGINT]) → T

With:

• T : Floating-point type (FLOAT, DOUBLE)

Signature notes:

• Uses "round half up" strategy (0.5 rounds up to 1)
• Negative decimal_places rounds to tens, hundreds, etc.
• Returns NULL if the input is NULL

Integer

Returns: Same type as the input

ROUND(number: T, [decimal_places: BIGINT]) → T

With:

• T : Integer type (TINYINT … BIGINT)

Signature notes:

• ROUND on an integer with decimal_places ≥ 0 is a no-op
• Negative decimal_places rounds to tens, hundreds, etc.

Examples

Decimal

SELECT
    f1 := ROUND(1.234::DECIMAL(5,3), 1), -- Literal decimal_places: inferred DECIMAL(5,1)
    f2 := ROUND(1.234::DECIMAL(5,3), -1), -- Negative decimal_places: inferred DECIMAL(5,0)
    f3 := ROUND(99.5::DECIMAL(5,1), -1) -- Negative decimal_places with carry
;

Floating-point

SELECT
    f1 := ROUND(2.4E0), -- Round down below 0.5
    f2 := ROUND(2.5E0), -- Round half up
    f3 := ROUND(-2.6E0), -- Negative rounds away from zero
    f4 := ROUND(123.456E0, 2), -- Two fractional digits
    f5 := ROUND(FLOAT '2.4') -- Float stays float
;

Negative decimal_places

SELECT
    f1 := ROUND(123.456E0, -1) -- Round to tens
;

Integer

SELECT
    f1 := ROUND(15, 1), -- No-op with positive decimal_places
    f2 := ROUND(1234, -2) -- Round to hundreds
;