---
title: Rounding Functions reference
meta:
    description: Functions for rounding numeric values.
headingMaxLevels: 2
---

# Rounding functions

The following functions are used to round numbers.

## floor

Returns the largest integer less than or equal to `x`, or rounds `x` down to a specified number of decimal places `N`. If `N` is negative, it rounds to the left of the decimal point.

### Syntax

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

### Arguments

- `x`: The value to round. Can be a [Float](../data-types/float), [Decimal](../data-types/decimal), or [Int/UInt](../data-types/int-uint) type.
- `N`: An integer specifying the number of decimal places. Defaults to `0` for rounding to a whole number. Can be negative to round to the left of the decimal point.

### Returns

A rounded number of the same type as `x`.

### Example

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

Result:

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

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

Result:

```result
┌─rounded─┐
│     120 │
└─────────┘
```

## ceiling

Returns the smallest integer greater than or equal to `x`, or rounds `x` up to a specified number of decimal places `N`. If `N` is negative, it rounds to the left of the decimal point.

Alias: `ceil`

### Syntax

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

### Arguments

- `x`: The value to round. Can be a [Float](../data-types/float), [Decimal](../data-types/decimal), or [Int/UInt](../data-types/int-uint) type.
- `N`: An integer specifying the number of decimal places. Defaults to `0` for rounding to a whole number. Can be negative to round to the left of the decimal point.

### Returns

A rounded number of the same type as `x`.

### Example

```sql
SELECT ceiling(123.45, 1) AS rounded_up
```

Result:

```result
┌─rounded_up─┐
│      123.5 │
└────────────┘
```

## truncate

Truncates a number `x` by removing its fractional part, or rounds it towards zero to a specified number of decimal places `N`. If `N` is negative, it truncates to the left of the decimal point.

Alias: `trunc`

### Syntax

```sql
truncate(x[, N])
```

### Arguments

- `x`: The value to truncate. Can be a [Float](../data-types/float), [Decimal](../data-types/decimal), or [Int/UInt](../data-types/int-uint) type.
- `N`: An integer specifying the number of decimal places. Defaults to `0` for truncating to a whole number. Can be negative to truncate to the left of the decimal point.

### Returns

A truncated number of the same type as `x`.

### Example

```sql
SELECT truncate(123.499, 1) as res
```

Result:

```result
┌───res─┐
│ 123.4 │
└───────┘
```

## round

Rounds a number `x` to the nearest value with `N` decimal places. For floating-point numbers, it uses banker's rounding when `x` is exactly halfway between two numbers. For other numeric types, it rounds away from zero in such cases.

### Syntax

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

### Arguments

- `x`: The numeric value to round. Can be a [Float](../data-types/float), [Decimal](../data-types/decimal), or [Int/UInt](../data-types/int-uint) type.
- `N`: An integer specifying the number of decimal places. Defaults to `0` for rounding to the nearest whole number. Positive `N` rounds to the right of the decimal point, negative `N` rounds to the left.

### Returns

A rounded number of the same type as `x`.

### Example

Example with `Float` inputs:

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

Result:

```result
┌───x─┬─round(divide(number, 2))─┐
│   0 │                        0 │
│ 0.5 │                        0 │
│   1 │                        1 │
└─────┴──────────────────────────┘
```

Example with `Decimal` inputs:

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

Result:

```result
┌───x─┬─round(CAST(divide(number, 2), 'Decimal(10, 4)'))─┐
│   0 │                                                0 │
│ 0.5 │                                                1 │
│   1 │                                                1 │
└─────┴──────────────────────────────────────────────────┘
```

To retain trailing zeros, enable setting `output_format_decimal_trailing_zeros`:

```sql
SELECT cast(number / 2 AS  Decimal(10,4)) AS x, round(x) FROM system.numbers LIMIT 3 settings output_format_decimal_trailing_zeros=1
```

Result:

```result
┌──────x─┬─round(CAST(divide(number, 2), 'Decimal(10, 4)'))─┐
│ 0.0000 │                                           0.0000 │
│ 0.5000 │                                           1.0000 │
│ 1.0000 │                                           1.0000 │
└────────┴──────────────────────────────────────────────────┘
```

## roundBankers

Rounds a number `x` to `N` decimal places using banker's rounding. This method rounds halfway values to the nearest even digit. For example, 2.5 rounds to 2, and 3.5 rounds to 4. This helps minimize cumulative rounding errors in calculations.

### Syntax

```sql
roundBankers(x [, N])
```

### Arguments

- `x`: The numeric value to round. Can be a [Float](../data-types/float), [Decimal](../data-types/decimal), or [Int/UInt](../data-types/int-uint) type.
- `N`: An integer specifying the number of decimal places. Defaults to `0` for rounding to the nearest whole number. Positive `N` rounds to the right of the decimal point, negative `N` rounds to the left.

### Returns

A value rounded by the banker's rounding method.

### Example

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

Result:

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

## roundToExp2

Rounds a number `num` down to the nearest power of two. If `num` is less than 1, it returns 0.

### Syntax

```sql
roundToExp2(num)
```

### Arguments

- `num`: The number to round down to a power of two. Can be a [UInt](../data-types/int-uint) or [Float](../data-types/float) type.

### Returns

Returns `0` if `num` is less than 1 (type `UInt8`). Otherwise, returns `num` rounded down to the nearest power of two (type matches input `UInt` or `Float`).

### Example

```sql
SELECT number, roundToExp2(number) FROM system.numbers WHERE number IN (0, 2, 5, 10, 19, 50)
```

Result:

```result
┌─number─┬─roundToExp2(number)─┐
│      0 │                   0 │
│      2 │                   2 │
│      5 │                   4 │
│     10 │                   8 │
│     19 │                  16 │
│     50 │                  32 │
└────────┴─────────────────────┘
```

## roundDuration

Rounds a number `num` down to the closest value from a predefined set of common duration intervals. If `num` is less than 1, it returns 0.

### Syntax

```sql
roundDuration(num)
```

### Arguments

- `num`: The number representing a duration to round. Can be a [UInt](../data-types/int-uint) or [Float](../data-types/float) type.

### Returns

Returns `0` if `num` is less than 1. Otherwise, returns one of the predefined duration values: `1, 10, 30, 60, 120, 180, 240, 300, 600, 1200, 1800, 3600, 7200, 18000, 36000`. Type: [UInt16](../data-types/int-uint).

### Example

```sql
SELECT number, roundDuration(number) FROM system.numbers WHERE number IN (0, 9, 19, 47, 101, 149, 205, 271, 421, 789, 1423, 2345, 4567, 9876, 24680, 42573)
```

Result:

```result
┌─number─┬─roundDuration(number)─┐
│      0 │                     0 │
│      9 │                     1 │
│     19 │                    10 │
│     47 │                    30 │
│    101 │                    60 │
│    149 │                   120 │
│    205 │                   180 │
│    271 │                   240 │
│    421 │                   300 │
│    789 │                   600 │
│   1423 │                  1200 │
│   2345 │                  1800 │
│   4567 │                  3600 │
│   9876 │                  7200 │
│  24680 │                 18000 │
│  42573 │                 36000 │
└────────┴───────────────────────┘
```

## roundAge

Categorizes an age `num` into predefined age groups and returns the lower bound of that group, or a specific value for certain ranges.

### Syntax

```sql
roundAge(num)
```

### Arguments

- `age`: A number representing an age in years. Can be a [UInt](../data-types/int-uint) or [Float](../data-types/float) type.

### Returns

Returns `0` for ages less than 1. Returns `17` for ages 1-17. Returns `18` for ages 18-24. Returns `25` for ages 25-34. Returns `35` for ages 35-44. Returns `45` for ages 45-54. Returns `55` for ages 55 or greater. Type: [UInt8](../data-types/int-uint).

### Example

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

Result:

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

## roundDown

Rounds a number `num` down to the largest value in a provided array `arr` that is less than or equal to `num`. If `num` is smaller than all elements in `arr`, it returns the smallest element from `arr`.

### Syntax

```sql
roundDown(num, arr)
```

### Arguments

- `num`: The numeric value to round down. Can be a [Numeric](../data-types/int-uint) type.
- `arr`: An [Array](../data-types/array) of numeric values ([UInt](../data-types/int-uint) or [Float](../data-types/float)) to use as rounding boundaries.

### Returns

The `num` value rounded down to an element in `arr`. If `num` is less than the smallest element in `arr`, the smallest element is returned. The return type matches the element type of `arr`.

### Example

```sql
SELECT number, roundDown(number, [3, 4, 5]) FROM system.numbers WHERE number IN (0, 1, 2, 3, 4, 5)
```

Result:

```result
┌─number─┬─roundDown(number, [3, 4, 5])─┐
│      0 │                            3 │
│      1 │                            3 │
│      2 │                            3 │
│      3 │                            3 │
│      4 │                            4 │
│      5 │                            5 │
└────────┴──────────────────────────────┘
```