ROUND Function in BigQuery

The ROUND function in BigQuery is a mathematical function used to round a numeric value to a specified number of decimal places. It is essential for cleaning data, generating reports, and simplifying complex decimal calculations for better readability.

Syntax

SQL

ROUND(numeric_expression [, digits_to_round])

• numeric_expression: The numeric value (FLOAT64 or NUMERIC) you want to round.
• digits_to_round (optional): The number of decimal places to round to. If omitted, it defaults to 0 (rounds to the nearest integer).

Example 1: Rounding to the nearest integer (default behavior).

SQL

-- Rounds 123.456 to 123 (nearest integer)
SELECT ROUND(123.456) AS rounded_value;

Example 2: Rounding to a specific number of decimal places.

SQL

-- Rounds 123.456 to 123.46 (rounds up because the 3rd decimal is 6)
SELECT ROUND(123.456, 2) AS rounded_value;

Example 3: Using negative digits to round to the nearest ten or hundred.

SQL

-- Rounds 123.456 to the nearest hundred (100)
SELECT ROUND(123.456, -2) AS rounded_value;

Note: If the digit to the right of the rounding position is 5 or greater, BigQuery rounds the value away from zero (up for positive numbers, down for negative).