ROUND

Round a numeric value to a specified number of decimal places using half-up (away-from-zero) rounding.

Category: mathReturns: DOUBLEDialect: Standard

Syntax

ROUND(expr [, d])

Description

## Overview Returns the input rounded to the specified number of decimal places using half-away-from-zero rounding: when the digit being dropped is exactly 5, the remaining digit is rounded away from zero (2.5 becomes 3, -2.5 becomes -3). This convention is familiar from grade-school arithmetic and from most general-purpose ROUND functions in SQL engines. ROUND is used everywhere currencies, invoices, and human-facing reports appear. For financial reporting that must minimize cumulative rounding bias, consider BROUND or PYTHON_ROUND (banker's rounding) instead. ## Behavior - Accepts any numeric type for the value. The return type follows the input type's rules. - The scale argument is optional; when omitted the value is rounded to the nearest integer-valued DOUBLE or DECIMAL. - Returns NULL if either argument is NULL. - Negative scale values round to the left of the decimal point (tens, hundreds). - Uses half-away-from-zero rounding (2.5 -> 3, -2.5 -> -3, 2.45 -> 2.5, 2.44 -> 2.4). ## Numeric precision - For DECIMAL inputs, rounding is exact. - For DOUBLE inputs, the value being rounded is already subject to binary floating-point representation. ROUND(2.675, 2) may return 2.67 rather than 2.68 because 2.675 cannot be represented exactly in binary. - When exactness at a specific decimal scale is required, use DECIMAL columns rather than DOUBLE. ## Compatibility - Conforms to the SQL standard scalar function ROUND. - The half-away-from-zero convention is the default in most SQL dialects. Dialects that use banker's rounding by default expose it under separate functions (BROUND, PYTHON_ROUND).

Parameters

Name	Type	Description
`expr`		Specifies the numeric value to round. Accepts any numeric type; the return type generally matches the input type (DECIMAL returns DECIMAL with adjusted scale; DOUBLE returns DOUBLE).
`d`		Specifies the number of decimal places. Defaults to 0. Positive values round to the right of the decimal point; negative values round to the left (tens, hundreds, and so on).

Examples

-- Basic literal: round to nearest integer
SELECT ROUND(2.5);
-- Result: 3.0

-- Round to 2 decimal places
SELECT ROUND(3.14159, 2);
-- Result: 3.14

-- Explicit zero decimal places
SELECT ROUND(7.8, 0);
-- Result: 8.0

-- Negative decimal places: round to tens
SELECT ROUND(1234, -2);
-- Result: 1200.0

-- Negative halfway value rounds away from zero
SELECT ROUND(-2.5);
-- Result: -3.0

-- NULL propagation
SELECT ROUND(NULL, 2);
-- Result: NULL

Pitfalls

ROUND on DOUBLE inputs is surprising: ROUND(2.675, 2) returns 2.67 because 2.675 is not exactly representable in binary floating-point. Use DECIMAL storage for money.
ROUND uses half-away-from-zero. For cumulative-bias-free rounding in financial aggregates, use BROUND (banker's rounding / round half to even).
Negative scale is allowed and rounds to the left of the decimal point: ROUND(1234, -2) = 1200. Do not assume negative scale is an error.
ROUND preserves DECIMAL scale. ROUND(CAST(1.5 AS DECIMAL(10,2)), 0) returns 2.00, not 2. Cast to an integer type if you want the integer value.
ROUND does not propagate NaN to an error; NaN input yields NaN output in DOUBLE arithmetic, which then silently propagates through aggregates.