> ## Documentation Index
> Fetch the complete documentation index at: https://docs.e6data.com/llms.txt
> Use this file to discover all available pages before exploring further.

# AGGREGATE

> Combines array elements into a running value, with an optional final transformation.

<div className="e6-meta"><span className="e6-eyebrow e6-eyebrow--quiet">Scalar function</span></div>

**Aliases:** [`reduce`](/query/sql/functions/array/reduce)

NULL array returns NULL. NULL elements and a NULL initial state are passed to lambdas; the lambda determines resulting NULL behavior.

## Syntax

### Scalar

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
aggregate(array, initial, merge [, finish])
```

## Arguments

Type notation used below:

* T is the array element type: BOOLEAN, TINYINT, SMALLINT, INTEGER, BIGINT, FLOAT, DOUBLE, DECIMAL(p, s), STRING, BINARY, DATE, TIME, TIMESTAMP, ARRAY, MAP or STRUCT.
* S is the initial running value type: BOOLEAN, TINYINT, SMALLINT, INTEGER, BIGINT, FLOAT, DOUBLE, DECIMAL(p, s), STRING, BINARY, DATE, TIME, TIMESTAMP, ARRAY, MAP or STRUCT.
* U is the result type of the lambda or finish expression: BOOLEAN, TINYINT, SMALLINT, INTEGER, BIGINT, FLOAT, DOUBLE, DECIMAL(p, s), STRING, BINARY, DATE, TIME, TIMESTAMP, ARRAY, MAP or STRUCT.

| Name | Type | Required | Description |
| - | - | - | - |
| array | ARRAY\<T> | Yes | Input elements. Nested elements/fields must satisfy the same element-type restrictions. |
| initial | S | Yes | The initial running value. The merge expression must return this same type. |
| merge | FUNCTION(S, T) -> S | Yes | A lambda that combines the running value with the next array element. It must return the initial value’s type, S. |
| finish | FUNCTION(S) -> U | No | A lambda that transforms the final running value into a result of type U. If omitted, the final running value is returned unchanged. |

## Returns

* Type: S without finish; U with finish
* Possible values: The final running value or finish result.
* NULL behavior: NULL array returns NULL. NULL elements and a NULL initial state are passed to lambdas; the lambda determines resulting NULL behavior.
* Empty input: For an empty array, returns initial or finish(initial).
* Shape: The initial value’s type or finish return type determines result shape.
* Ordering: Merge processes elements from first to last.

## Properties

| Property | Value |
| - | - |
| Deterministic | Partial (Depends on whether the merge and finish expressions give repeatable results.) |
| Supports window usage | No |

## Usage notes

* An empty array yields the initial state, or finish(initial) when finish is supplied.
* The merge expression must return exactly the same type as the initial value. A different type raises an error.
* INTEGER and INT mean the same type; STRING means VARCHAR text. NULL can be used with a supported type. Cast NULL explicitly when its type would otherwise be unclear.
* Query tools may report a DECIMAL result as DOUBLE or a TIME result as INTEGER. The returned value still keeps its decimal precision and scale, or its time type. A reported type does not convert the value.

## Examples

### Typical behavior

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
SELECT aggregate(ARRAY[1, 2, 3], 0, (acc, x) -> acc + x) AS "value";
```

| value |
| - |
| 6 |

### Apply a final transformation

The merge adds the elements to produce 6. The optional finish expression multiplies that final value by 10.

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
SELECT aggregate(ARRAY[1, 2, 3], 0, (acc, x) -> acc + x, acc -> acc * 10) AS result;
```

| result |
| - |
| 60 |


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.