Aggregate function combinators

Type depends on the aggregate function used.

**Example**

Query:

```
SELECT avg(number), avgOrDefault(number) FROM numbers(0)
```

Result:

```
┌─avg(number)─┬─avgOrDefault(number)─┐
│         nan │                    0 │
└─────────────┴──────────────────────┘
```

Also `-OrDefault` can be used with another combinators. It is useful when the aggregate function does not accept the empty input.

Query:

```
SELECT avgOrDefaultIf(x, x > 10)
FROM
(
    SELECT toDecimal32(1.23, 2) AS x
)
```

Result:

```
┌─avgOrDefaultIf(x, greater(x, 10))─┐
│                              0.00 │
└───────────────────────────────────┘
```

## -OrNull[​](#-ornull "Direct link to -OrNull")

Changes behavior of an aggregate function.

This combinator converts a result of an aggregate function to the [Nullable](/docs/sql-reference/data-types/nullable) data type. If the aggregate function does not have values to calculate it returns [NULL](/docs/operations/settings/formats#input_format_null_as_default).

`-OrNull` can be used with other combinators.

**Syntax**

```
<aggFunction>OrNull(x)
```

**Arguments**

- `x` — Aggregate function parameters.

**Returned values**

- The result of the aggregate function, converted to the `Nullable` data type.
- `NULL`, if there is nothing to aggregate.

Type: `Nullable(aggregate function return type)`.

**Example**

Add `-orNull` to the end of aggregate function.

Query:

```
SELECT sumOrNull(number), toTypeName(sumOrNull(number)) FROM numbers(10) WHERE number > 10
```

Result:

```
┌─sumOrNull(number)─┬─toTypeName(sumOrNull(number))─┐
│              ᴺᵁᴸᴸ │ Nullable(UInt64)              │
└───────────────────┴───────────────────────────────┘
```

Also `-OrNull` can be used with another combinators. It is useful when the aggregate function does not accept the empty input.

Query:

```
SELECT avgOrNullIf(x, x > 10)
FROM
(
    SELECT toDecimal32(1.23, 2) AS x
)
```

Result:

```
┌─avgOrNullIf(x, greater(x, 10))─┐
│                           ᴺᵁᴸᴸ │
└────────────────────────────────┘
```

## -Resample[​](#-resample "Direct link to -Resample")

Lets you divide data into groups, and then separately aggregates the data in those groups. Groups are created by splitting the values from one column into intervals.

```
<aggFunction>Resample(start, end, step)(<aggFunction_params>, resampling_key)
```

**Arguments**

- `start` — Starting value of the whole required interval for `resampling_key` values.
- `stop` — Ending value of the whole required interval for `resampling_key` values. The whole interval does not include the `stop` value `[start, stop)`.
- `step` — Step for separating the whole interval into subintervals. The `aggFunction` is executed over each of those subintervals independently.
- `resampling_key` — Column whose values are used for separating data into intervals.
- `aggFunction_params` — `aggFunction` parameters.

**Returned values**

- Array of `aggFunction` results for each subinterval.

**Example**

Consider the `people` table with the following data:

```
┌─name───┬─age─┬─wage─┐
│ John   │  16 │   10 │
│ Alice  │  30 │   15 │
│ Mary   │  35 │    8 │
│ Evelyn │  48 │ 11.5 │
│ David  │  62 │  9.9 │
│ Brian  │  60 │   16 │
└────────┴─────┴──────┘
```

Let's get the names of the people whose age lies in the intervals of `[30,60)` and `[60,75)`. Since we use integer representation for age, we get ages in the `[30, 59]` and `[60,74]` intervals.

To aggregate names in an array, we use the [groupArray](/docs/sql-reference/aggregate-functions/reference/grouparray) aggregate function. It takes one argument. In our case, it's the `name` column. The `groupArrayResample` function should use the `age` column to aggregate names by age. To define the required intervals, we pass the `30, 75, 30` arguments into the `groupArrayResample` function.

```
SELECT groupArrayResample(30, 75, 30)(name, age) FROM people
```

```
┌─groupArrayResample(30, 75, 30)(name, age)─────┐
│ [['Alice','Mary','Evelyn'],['David','Brian']] │
└───────────────────────────────────────────────┘
```

Consider the results.

`John` is out of the sample because he's too young. Other people are distributed according to the specified age intervals.

Now let's count the total number of people and their average wage in the specified age intervals.

```
SELECT
    countResample(30, 75, 30)(name, age) AS amount,
    avgResample(30, 75, 30)(wage, age) AS avg_wage
FROM people
```

```
┌─amount─┬─avg_wage──────────────────┐
│ [3,2]  │ [11.5,12.949999809265137] │
└────────┴───────────────────────────┘
```

## -ArgMin[​](#-argmin "Direct link to -ArgMin")

The suffix -ArgMin can be appended to the name of any aggregate function. In this case, the aggregate function accepts an additional argument, which should be any comparable expression. The aggregate function processes only the rows that have the minimum value for the specified extra expression.

Examples: `sumArgMin(column, expr)`, `countArgMin(expr)`, `avgArgMin(x, expr)` and so on.

## -ArgMax[​](#-argmax "Direct link to -ArgMax")

Similar to suffix -ArgMin but processes only the rows that have the maximum value for the specified extra expression.

## Related Content[​](#related-content "Direct link to Related Content")

- Blog: [Using Aggregate Combinators in ClickHouse](https://clickhouse.com/blog/aggregate-functions-combinators-in-clickhouse-for-arrays-maps-and-states)

- [-If](#-if)- [-Array](#-array)- [-Map](#-map)- [-SimpleState](#-simplestate)- [-State](#-state)- [-Merge](#-merge)- [-MergeState](#-mergestate)- [-ForEach](#-foreach)- [-Distinct](#-distinct)- [-OrDefault](#-ordefault)- [-OrNull](#-ornull)- [-Resample](#-resample)- [-ArgMin](#-argmin)- [-ArgMax](#-argmax)- [Related Content](#related-content)

Was this page helpful?