Rounding functions

[Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/sql-reference/functions/rounding-functions.md)

Note

The documentation below is generated from the `system.functions` system table

## ceil[​](#ceil "Direct link to ceil")

Introduced in: v1.1.0

Like [`floor`](#floor) but returns the smallest rounded number greater than or equal to `x`.
If rounding causes an overflow (for example, `ceiling(255, -1)`), the result is undefined.

**Syntax**

```
ceiling(x[, N])
```

**Aliases**: `ceiling`

**Arguments**

- `x` — The value to round. [`Float*`](/docs/sql-reference/data-types/float) or [`Decimal*`](/docs/sql-reference/data-types/decimal) or [`(U)Int*`](/docs/sql-reference/data-types/int-uint)
- `N` — Optional. The number of decimal places to round to. Defaults to zero, which means rounding to an integer. Can be negative. [`(U)Int*`](/docs/sql-reference/data-types/int-uint)

**Returned value**

Returns a rounded number of the same type as `x`. [`Float*`](/docs/sql-reference/data-types/float) or [`Decimal*`](/docs/sql-reference/data-types/decimal) or [`(U)Int*`](/docs/sql-reference/data-types/int-uint)

**Examples**

**Basic usage**

```
SELECT ceiling(123.45, 1) AS rounded
```

```
┌─rounded─┐
│   123.5 │
└─────────┘
```

**Negative precision**

```
SELECT ceiling(123.45, -1)
```

```
┌─ceiling(123.45, -1)─┐
│                 130 │
└─────────────────────┘
```

## floor[​](#floor "Direct link to floor")

Introduced in: v1.1.0

Returns the largest rounded number less than or equal to `x`, where the rounded number is a multiple of `1 / 10 * N`, or the nearest number of the appropriate data type if `1 / 10 * N` isn't exact.

Integer arguments may be rounded with a negative `N` argument.
With non-negative `N` the function returns `x`.

If rounding causes an overflow (for example, `floor(-128, -1)`), the result is undefined.

**Syntax**

```
floor(x[, N])
```

**Arguments**

- `x` — The value to round. [`Float*`](/docs/sql-reference/data-types/float) or [`Decimal*`](/docs/sql-reference/data-types/decimal) or [`(U)Int*`](/docs/sql-reference/data-types/int-uint)
- `N` — Optional. The number of decimal places to round to. Defaults to zero, which means rounding to an integer. Can be negative. [`(U)Int*`](/docs/sql-reference/data-types/int-uint)

**Returned value**

Returns a rounded number of the same type as `x`. [`Float*`](/docs/sql-reference/data-types/float) or [`Decimal*`](/docs/sql-reference/data-types/decimal) or [`(U)Int*`](/docs/sql-reference/data-types/int-uint)

**Examples**

**Usage example**

```
SELECT floor(123.45, 1) AS rounded
```

```
┌─rounded─┐
│   123.4 │
└─────────┘
```

**Negative precision**

```
SELECT floor(123.45, -1)
```

```
┌─floor(123.45, -1)─┐
│               120 │
└───────────────────┘
```

## round[​](#round "Direct link to round")

Introduced in: v1.1.0

Rounds a value to a specified number of decimal places `N`.

- If `N > 0`, the function rounds to the right of the decimal point.
- If `N < 0`, the function rounds to the left of the decimal point.
- If `N = 0`, the function rounds to the next integer.

The function returns the nearest number of the specified order.
If the input value has equal distance to two neighboring numbers, the function uses banker's rounding for `Float*` inputs and rounds away from zero for the other number types (`Decimal*`).

If rounding causes an overflow (for example, `round(255, -1)`), the result is undefined.

**Syntax**

```
round(x[, N])
```

**Arguments**

- `x` — A number to round. [`Float*`](/docs/sql-reference/data-types/float) or [`Decimal*`](/docs/sql-reference/data-types/decimal) or [`(U)Int*`](/docs/sql-reference/data-types/int-uint)
- `N` — Optional. The number of decimal places to round to. Defaults to `0`. [`(U)Int*`](/docs/sql-reference/data-types/int-uint)

**Returned value**

Returns a rounded number of the same type as `x`. [`Float*`](/docs/sql-reference/data-types/float) or [`Decimal*`](/docs/sql-reference/data-types/decimal) or [`(U)Int*`](/docs/sql-reference/data-types/int-uint)

**Examples**

**Float inputs**

```
SELECT number / 2 AS x, round(x) FROM system.numbers LIMIT 3;
```

```
┌───x─┬─round(x)─┐
│   0 │        0 │
│ 0.5 │        0 │
│   1 │        1 │
└─────┴──────────┘
```

**Decimal inputs**

```
SELECT cast(number / 2 AS  Decimal(10,4)) AS x, round(x) FROM system.numbers LIMIT 3;
```

```
┌───x─┬─round(x)─┐
│   0 │        0 │
│ 0.5 │        1 │
│   1 │        1 │
└─────┴──────────┘
```

## roundAge[​](#roundAge "Direct link to roundAge")

Introduced in: v1.1.0

Takes a number representing a human age, compares it to standard age ranges, and returns either the highest or lowest value of the range the number falls within.

- Returns `0`, for `age < 1`.
- Returns `17`, for `1 ≤ age ≤ 17`.
- Returns `18`, for `18 ≤ age ≤ 24`.
- Returns `25`, for `25 ≤ age ≤ 34`.
- Returns `35`, for `35 ≤ age ≤ 44`.
- Returns `45`, for `45 ≤ age ≤ 54`.
- Returns `55`, for `age ≥ 55`.

**Syntax**

```
roundAge(num)
```

**Arguments**

- `age` — A number representing an age in years. [`(U)Int*`](/docs/sql-reference/data-types/int-uint) or [`Float*`](/docs/sql-reference/data-types/float)

**Returned value**

Returns either the highest or lowest age of the range `age` falls within. [`UInt8`](/docs/sql-reference/data-types/int-uint)

**Examples**

**Usage example**

```
SELECT *, roundAge(*) FROM system.numbers WHERE number IN (0, 5, 20, 31, 37, 54, 72);
```

```
┌─number─┬─roundAge(number)─┐
│      0 │                0 │
│      5 │               17 │
│     20 │               18 │
│     31 │               25 │
│     37 │               35 │
│     54 │               45 │
│     72 │               55 │
└────────┴──────────────────┘
```

## roundBankers[​](#roundBankers "Direct link to roundBankers")

Introduced in: v20.1.0

Rounds a number to a specified decimal position `N`.
If the rounding number is halfway between two numbers, the function uses a method of rounding called banker's rounding, which is the default rounding method for floating point numbers defined in IEEE 754.

- If `N > 0`, the function rounds to the right of the decimal point
- If `N < 0`, the function rounds to the left of the decimal point
- If `N = 0`, the function rounds to the next integer

Notes

- 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`.
- The `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.

Use banker's rounding for summation or subtraction of numbers

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(x[, N])
```

**Arguments**

- `x` — A number to round. [`(U)Int*`](/docs/sql-reference/data-types/int-uint) or [`Decimal*`](/docs/sql-reference/data-types/decimal) or [`Float*`](/docs/sql-reference/data-types/float)
- `[, N]` — Optional. The number of decimal places to round to. Defaults to `0`. [`(U)Int*`](/docs/sql-reference/data-types/int-uint)

**Returned value**

Returns a value rounded by the banker's rounding method. [`(U)Int*`](/docs/sql-reference/data-types/int-uint) or [`Decimal*`](/docs/sql-reference/data-types/decimal) or [`Float*`](/docs/sql-reference/data-types/float)

**Examples**

**Basic usage**

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

```
┌───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 │
└─────┴───┘
```

## roundDown[​](#roundDown "Direct link to roundDown")

Introduced in: v20.1.0