Arithmetic functions

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

## Overview[​](#overview "Direct link to Overview")

Arithmetic functions work for any two operands of type `UInt8`, `UInt16`, `UInt32`, `UInt64`, `Int8`, `Int16`, `Int32`, `Int64`, `Float32`, or `Float64`.

Before performing the operation, both operands are cast to the result type. The result type is determined as follows (unless specified
differently in the function documentation below):

- If both operands are up to 32 bits wide, the size of the result type will be the size of the next bigger type following the bigger of the
  two operands (integer size promotion). For example, `UInt8 + UInt16 = UInt32` or `Float32 * Float32 = Float64`.
- If one of the operands has 64 or more bits, the size of the result type will be the same size as the bigger of the two operands. For
  example, `UInt32 + UInt128 = UInt128` or `Float32 * Float64 = Float64`.
- If one of the operands is signed, the result type will also be signed, otherwise it will be unsigned. For example, `UInt32 * Int32 = Int64` or `UInt32 * UInt32 = UInt64`.

These rules make sure that the result type will be the smallest type which can represent all possible results. While this introduces a risk
of overflows around the value range boundary, it ensures that calculations are performed quickly using the maximum native integer width of
64 bit. This behavior also guarantees compatibility with many other databases which provide 64 bit integers (BIGINT) as the biggest integer
type.

Example:

```
SELECT toTypeName(0), toTypeName(0 + 0), toTypeName(0 + 0 + 0), toTypeName(0 + 0 + 0 + 0)
```

```
┌─toTypeName(0)─┬─toTypeName(plus(0, 0))─┬─toTypeName(plus(plus(0, 0), 0))─┬─toTypeName(plus(plus(plus(0, 0), 0), 0))─┐
│ UInt8         │ UInt16                 │ UInt32                          │ UInt64                                   │
└───────────────┴────────────────────────┴─────────────────────────────────┴──────────────────────────────────────────┘
```

Overflows are produced the same way as in C++.

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

Introduced in: v1.1.0

Calculates the absolute value of `x`. Has no effect if `x` is of an unsigned type. If `x` is of a signed type, it returns an unsigned number.

**Syntax**

```
abs(x)
```

**Arguments**

- `x` — Value to get the absolute value of

**Returned value**

The absolute value of `x`

**Examples**

**Usage example**

```
SELECT abs(-0.5)
```

```
0.5
```

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

Introduced in: v25.11.0

Computes and returns the average value of the provided arguments.
Supports numerical and temporal types.

**Syntax**

```
avg2(x1, x2])
```

**Arguments**

- `x1, x2]` — Accepts two values for averaging.

**Returned value**

Returns the average value of the provided arguments, promoted to the largest compatible type.

**Examples**

**Numeric types**

```
SELECT avg2(toUInt8(3), 1.0) AS result, toTypeName(result) AS type;
-- The type returned is a Float64 as the UInt8 must be promoted to 64 bit for the comparison.
```

```
┌─result─┬─type────┐
│      2 │ Float64 │
└────────┴─────────┘
```

**Decimal types**

```
SELECT avg2(toDecimal32(1, 2), 2) AS result, toTypeName(result) AS type;
```

```
┌─result─┬─type──────────┐
│    1.5 │ Decimal(9, 2) │
└────────┴───────────────┘
```

**Date types**

```
SELECT avg2(toDate('2025-01-01'), toDate('2025-01-05')) AS result, toTypeName(result) AS type;
```

```
┌─────result─┬─type─┐
│ 2025-01-03 │ Date │
└────────────┴──────┘
```

**DateTime types**

```
SELECT avg2(toDateTime('2025-01-01 00:00:00'), toDateTime('2025-01-03 12:00:00')) AS result, toTypeName(result) AS type;
```

```
┌──────────────result─┬─type─────┐
│ 2025-01-02 06:00:00 │ DateTime │
└─────────────────────┴──────────┘
```

**Time64 types**

```
SELECT avg2(toTime64('12:00:00', 0), toTime64('14:00:00', 0)) AS result, toTypeName(result) AS type;
```

```
┌───result─┬─type──────┐
│ 13:00:00 │ Time64(0) │
└──────────┴───────────┘
```

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

Introduced in: v23.10.0

Reverses the bytes of an integer, i.e. changes its [endianness](https://en.wikipedia.org/wiki/Endianness).

The below example can be worked out in the following manner:

1. Convert the base-10 integer to its equivalent hexadecimal format in big-endian format, i.e. 3351772109 -> C7 C7 FB CD (4 bytes)
2. Reverse the bytes, i.e. C7 C7 FB CD -> CD FB C7 C7
3. Convert the result back to an integer assuming big-endian, i.e. CD FB C7 C7 -> 3455829959
   One use case of this function is reversing IPv4s:

```
┌─toIPv4(byteSwap(toUInt32(toIPv4('205.251.199.199'))))─┐
│ 199.199.251.205                                       │
└───────────────────────────────────────────────────────┘
```

**Syntax**

```
byteSwap(x)
```

**Arguments**

- `x` — An integer value. [`(U)Int*`](/docs/sql-reference/data-types/int-uint)

**Returned value**

Returns `x` with bytes reversed. [`(U)Int*`](/docs/sql-reference/data-types/int-uint)

**Examples**

**Usage example**

```
SELECT byteSwap(3351772109)
```

```
3455829959
```

**8-bit**

```
SELECT byteSwap(54)
```

```
54
```

**16-bit**

```
SELECT byteSwap(4135)
```

```
10000
```

**32-bit**

```
SELECT byteSwap(3351772109)
```

```
3455829959
```

**64-bit**

```
SELECT byteSwap(123294967295)
```

```
18439412204227788800
```

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

Introduced in: v1.1.0

Calculates the quotient of two values `a` and `b`. The result type is always [Float64](/docs/sql-reference/data-types/float).
Integer division is provided by the `intDiv` function.

Note

Division by `0` returns `inf`, `-inf`, or `nan`.

**Syntax**

```
divide(x, y)
```

**Arguments**

- `x` — Dividend - `y` — Divisor

**Returned value**

The quotient of x and y

**Examples**

**Dividing two numbers**

```
SELECT divide(25,5) AS quotient, toTypeName(quotient)
```

```
5 Float64
```

**Dividing by zero**

```
SELECT divide(25,0)
```

```
inf
```

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

Introduced in: v22.12.0

Performs division on two decimals. Result value will be of type [Decimal256](/docs/sql-reference/data-types/decimal).
Result scale can be explicitly specified by `result_scale` argument (const Integer in range `[0, 76]`). If not specified, the result scale is the max scale of given arguments.

Note

These function work significantly slower than usual `divide`.
In case you don't really need controlled precision and/or need fast computation, consider using [divide](#divide).

**Syntax**

```
divideDecimal(x, y[, result_scale])
```

**Arguments**

- `x` — First value: [Decimal](/docs/sql-reference/data-types/decimal). - `y` — Second value: [Decimal](/docs/sql-reference/data-types/decimal). - `result_scale` — Scale of result. Type [Int/UInt](/docs/sql-reference/data-types/int-uint).

**Returned value**

The result of division with given scale. [`Decimal256`](/docs/sql-reference/data-types/decimal)

**Examples**

**Example 1**

```
divideDecimal(toDecimal256(-12, 0), toDecimal32(2.1, 1), 10)
```

```
┌─divideDecimal(toDecimal256(-12, 0), toDecimal32(2.1, 1), 10)─┐
│                                                -5.7142857142 │
└──────────────────────────────────────────────────────────────┘
```

**Example 2**

```
SELECT toDecimal64(-12, 1) / toDecimal32(2.1, 1);
SELECT toDecimal64(-12, 1) as a, toDecimal32(2.1, 1) as b, divideDecimal(a, b, 1), divideDecimal(a, b, 5);
```

```
┌─divide(toDecimal64(-12, 1), toDecimal32(2.1, 1))─┐
│                                             -5.7 │
└──────────────────────────────────────────────────┘
┌───a─┬───b─┬─divideDecimal(toDecimal64(-12, 1), toDecimal32(2.1, 1), 1)─┬─divideDecimal(toDecimal64(-12, 1), toDecimal32(2.1, 1), 5)─┐
│ -12 │ 2.1 │                                                       -5.7 │                                                   -5.71428 │
└─────┴─────┴────────────────────────────────────────────────────────────┴────────────────────────────────────────────────────────────┘
```

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

Introduced in: v25.5.0