# Rounding Functions

## floor(x[, N])β

Returns the largest round number that is less than or equal to `x`. A round number is a multiple of 1/10N, or the nearest number of the appropriate data type if 1 / 10N isnβt exact. βNβ is an integer constant, optional parameter. By default it is zero, which means to round to an integer. βNβ may be negative.

Examples: `floor(123.45, 1) = 123.4, floor(123.45, -1) = 120.`

`x` is any numeric type. The result is a number of the same type. For integer arguments, it makes sense to round with a negative `N` value (for non-negative `N`, the function does not do anything). If rounding causes overflow (for example, floor(-128, -1)), an implementation-specific result is returned.

## ceil(x[, N]), ceiling(x[, N])β

Returns the smallest round number that is greater than or equal to `x`. In every other way, it is the same as the `floor` function (see above).

## trunc(x[, N]), truncate(x[, N])β

Returns the round number with largest absolute value that has an absolute value less than or equal to `x`βs. In every other way, it is the same as the βfloorβ function (see above).

## round(x[, N])β

Rounds a value to a specified number of decimal places.

The function returns the nearest number of the specified order. In case when given number has equal distance to surrounding numbers, the function uses bankerβs rounding for float number types and rounds away from zero for the other number types (Decimal).

``round(expression [, decimal_places])``

Arguments

• `expression` β A number to be rounded. Can be any expression returning the numeric data type.
• `decimal-places` β An integer value.
• If `decimal-places > 0` then the function rounds the value to the right of the decimal point.
• If `decimal-places < 0` then the function rounds the value to the left of the decimal point.
• If `decimal-places = 0` then the function rounds the value to integer. In this case the argument can be omitted.

Returned value:

The rounded number of the same type as the input number.

### Examplesβ

Example of use with Float

``SELECT number / 2 AS x, round(x) FROM system.numbers LIMIT 3``
``ββββxββ¬βround(divide(number, 2))βββ   0 β                        0 ββ 0.5 β                        0 ββ   1 β                        1 ββββββββ΄βββββββββββββββββββββββββββ``

Example of use with Decimal

``SELECT cast(number / 2 AS  Decimal(10,4)) AS x, round(x) FROM system.numbers LIMIT 3``
``βββββββxββ¬βround(CAST(divide(number, 2), 'Decimal(10, 4)'))βββ 0.0000 β                                           0.0000 ββ 0.5000 β                                           1.0000 ββ 1.0000 β                                           1.0000 βββββββββββ΄βββββββββββββββββββββββββββββββββββββββββββββββββββ``

Examples of rounding

Rounding to the nearest number.

``round(3.2, 0) = 3round(4.1267, 2) = 4.13round(22,-1) = 20round(467,-2) = 500round(-467,-2) = -500``

Bankerβs rounding.

``round(3.5) = 4round(4.5) = 4round(3.55, 1) = 3.6round(3.65, 1) = 3.6``

## roundBankersβ

Rounds a number to a specified decimal position.

• If the rounding number is halfway between two numbers, the function uses bankerβs rounding.

``Banker's rounding is a method of rounding fractional numbers. When the rounding number is halfway between two numbers, it's rounded to the nearest even digit at the specified decimal position. For example: 3.5 rounds up to 4, 2.5 rounds down to 2.It's the default rounding method for floating point numbers defined in [IEEE 754](https://en.wikipedia.org/wiki/IEEE_754#Roundings_to_nearest). The [round](#rounding_functions-round) function performs the same rounding for floating point numbers. The `roundBankers` function also rounds integers the same way, for example, `roundBankers(45, -1) = 40`.``
• In other cases, the function rounds numbers to the nearest integer.

Using bankerβs rounding, you can reduce the effect that rounding numbers has on the results of summing or subtracting these numbers.

For example, sum numbers 1.5, 2.5, 3.5, 4.5 with different rounding:

• No rounding: 1.5 + 2.5 + 3.5 + 4.5 = 12.
• Bankerβs rounding: 2 + 2 + 4 + 4 = 12.
• Rounding to the nearest integer: 2 + 3 + 4 + 5 = 14.

Syntax

``roundBankers(expression [, decimal_places])``

Arguments

• `expression` β A number to be rounded. Can be any expression returning the numeric data type.
• `decimal-places` β Decimal places. An integer number.
• `decimal-places > 0` β The function rounds the number to the given position right of the decimal point. Example: `roundBankers(3.55, 1) = 3.6`.
• `decimal-places < 0` β The function rounds the number to the given position left of the decimal point. Example: `roundBankers(24.55, -1) = 20`.
• `decimal-places = 0` β The function rounds the number to an integer. In this case the argument can be omitted. Example: `roundBankers(2.5) = 2`.

Returned value

A value rounded by the bankerβs rounding method.

### Examplesβ

Example of use

Query:

`` SELECT number / 2 AS x, roundBankers(x, 0) AS b fROM system.numbers limit 10``

Result:

``ββββxββ¬βbβββ   0 β 0 ββ 0.5 β 0 ββ   1 β 1 ββ 1.5 β 2 ββ   2 β 2 ββ 2.5 β 2 ββ   3 β 3 ββ 3.5 β 4 ββ   4 β 4 ββ 4.5 β 4 ββββββββ΄ββββ``

Examples of Bankerβs rounding

``roundBankers(0.4) = 0roundBankers(-3.5) = -4roundBankers(4.5) = 4roundBankers(3.55, 1) = 3.6roundBankers(3.65, 1) = 3.6roundBankers(10.35, 1) = 10.4roundBankers(10.755, 2) = 10.76``

## roundToExp2(num)β

Accepts a number. If the number is less than one, it returns 0. Otherwise, it rounds the number down to the nearest (whole non-negative) degree of two.

## roundDuration(num)β

Accepts a number. If the number is less than one, it returns 0. Otherwise, it rounds the number down to numbers from the set: 1, 10, 30, 60, 120, 180, 240, 300, 600, 1200, 1800, 3600, 7200, 18000, 36000.

## roundAge(num)β

Accepts a number. If the number is less than 18, it returns 0. Otherwise, it rounds the number down to a number from the set: 18, 25, 35, 45, 55.

## roundDown(num, arr)β

Accepts a number and rounds it down to an element in the specified array. If the value is less than the lowest bound, the lowest bound is returned.