---
url: /v6.2/en-us/calculation-function-aggregate.md
---
# Aggregate Functions

Aggregate functions refer to statistical calculations performed on a set of values, returning a single numerical result. This article first prepares a table, and the following function examples will use this table for calculation results.

| f1 | f2 | f3 |
|----------|----------|----------|
| 10       | 20       | 30       |
| 20       | 30       | 40       |
| 30       | null     | 50       |
| 40       | 50       | 60       |
| 50       | 60       | 70       |
| 60       | 70       | null     |

## avg

| Syntax        | avg(N)                    |
|---------------|---------------------------|
| Parameter     | Numeric type parameter, optional type: number |
| Return Type   | Number                    |
| Description   | Returns the average value of the specified numeric column |
| Example       | avg({f1}), returns 35     |

## corr

| Syntax        | corr(ARG1, ARG2)          |
|---------------|---------------------------|
| Parameter Description | 1. ARG1, optional type: number;2. ARG2, optional type: number |
| Return Type   | Number                    |
| Description   | Returns the correlation coefficient between two numeric columns |
| Example       | (arg1, arg2) = \[(1,2),(2,3),(3,4),(4,5),(5,6),(1000,1001)], corr({arg1}, {arg2}), returns 1 |

## count

| Syntax        | count(ARG)                |
|---------------|--------------------------|
| Parameter     | The parameter to be calculated; optional types: any type |
| Return Type   | Number                   |
| Description   | Returns the number of values in the specified column |
| Example       | count({f2}), returns: 5 (null values are not counted) |

## covar_pop

| Syntax         | covar_pop(ARG1, ARG2)     |
|----------------|---------------------------|
| Parameter Description | 1. ARG1, optional type: number;2. ARG2, optional type: number |
| Return Type    | Number                    |
| Description    | Returns the population covariance of two numeric columns |
| Example        | (arg1, arg2) = \[(1,2),(2,3),(3,4),(4,5),(5,6),(1000,1001)], covar_pop({arg1}, {arg2}) returns 138058.47222222222  |

## covar_samp

| Syntax         | covar_samp(ARG1, ARG2)    |
|----------------|---------------------------|
| Parameter Description | 1. ARG1, optional type: number;2. ARG2, optional type: number |
| Return Type    | Number                    |
| Description    | Returns the sample covariance of two numeric columns |
| Example        | (arg1, arg2) = \[(1,2),(2,3),(3,4),(4,5),(5,6),(1000,1001)], covar_samp(arg1, arg2) returns 165670.16666666666 |

## distinct_count

| Syntax         | distinct_count(ARG)       |
|----------------|--------------------------|
| Parameter      | The parameter to be calculated; supported types: any type |
| Return Type    | Number                   |
| Description    | Returns the number of unique (non-duplicate) results in the specified column. On Impala data sources, this function uses approximate distinct counting and is suitable when performance is preferred on large data volumes. Use `exact_distinct_count` when exact distinct counting is required. |
| Example        | distinct_count({f2}), returns: 5 |
| Unsupported Data Sources | None                   |

## exact_distinct_count

| Syntax         | exact_distinct_count(ARG) |
|----------------|--------------------------|
| Parameter      | The parameter to be calculated; supported types: any type |
| Return Type    | Number                   |
| Description    | Returns the exact number of unique (non-duplicate) results in the specified column. It is currently used for exact distinct counting on Impala data sources. |
| Example        | exact_distinct_count({f2}), returns: 5 |
| Supported Data Sources | Impala             |
| Note           | This is an aggregate function and uses the same placement rules as `distinct_count`. It cannot be used as a normal field formula or subquery field. |

## first

| Syntax        | first(ARG)                |
|---------------|--------------------------|
| Parameter     | The parameter to be calculated, optional type: ANY |
| Return Type   | ANY                      |
| Description   | Retrieves the value of the first record in the specified column; the value is not fixed |
| Example       | first({f1}), returns: 10 |

## last

| Syntax        | last(ARG)                 |
|---------------|--------------------------|
| Parameter     | The parameter to be calculated; optional types: any type |
| Return Type   | Any type                 |
| Description   | Retrieves the value of the last record in the specified column. The value is not fixed. |
| Example       | last({f1}), returns: 60  |

## list_collect

| Syntax         | list_collect(ARG)         |
|----------------|--------------------------|
| Parameter      | The parameter to be calculated; optional types: any |
| Return Type    | Array                    |
| Description    | Aggregate function that collects the values of arg into an array. (The return format is an array type. If you see a null value, try converting it to a string to view it.) |
| Example        | list_collect({f1}), returns: an array containing all values of the f1 field |

## list_collect_flatten

| Syntax         | list_collect_flatten(ARR) |
|----------------|--------------------------|
| Parameter      | Array type parameter, optional type: array |
| Return Type    | Array                    |
| Description    | Aggregate function that collects and flattens the parameters of arr into an array. (The return format is an array. If you see a null value, try converting it to a string to view it.) |
| Example        | list_collect_flatten({f1}), returns: extracts all elements from the f1 array and combines them into a new array. |

## max

| Syntax         | max(ARG)                  |
|----------------|--------------------------|
| Parameter      | Parameter to be calculated, optional types: any type |
| Return Type    | Any type                 |
| Description    | Returns the maximum value of the specified numeric column |
| Example        | max({f1}), returns: 60   |
| Unsupported Data Sources | None           |

## max_by

| Syntax         | max_by(ARG, COMPARE)      |
|----------------|--------------------------|
| Parameter Description | 1. Parameter to be calculated, optional types: any type;2. Comparison value, optional types: any type |
| Return Type    | Any type                 |
| Description    | Returns the value of the arg column corresponding to the maximum value in the compare column. If there are multiple maximum values, one is selected at random. |
| Example        | max_by({f1}, if(isnull({f2}), -1, {f2})), returns: 60 |

## median

| Syntax        | median(ARG)               |
|---------------|--------------------------|
| Parameter     | Numeric parameter, optional type: number |
| Return Type   | Number                   |
| Description   | Returns the median of the specified numeric column |
| Example       | median({f1}), returns: 35 |

## min

| Syntax        | min(ARG)                  |
|---------------|---------------------------|
| Parameter     | The parameter to be calculated; optional types: any type |
| Return Type   | Any type                  |
| Description   | Returns the minimum value of the specified numeric column |
| Example       | min({f1}), returns: 10    |

## min_by

| Syntax         | min_by(ARG, COMPARE)      |
|----------------|--------------------------|
| Parameter Description | 1. Parameter to be calculated, optional type: any type;2. Comparison value, optional type: any type |
| Return Type    | Any type                 |
| Description    | Returns the value of the arg column corresponding to the row with the minimum value in the compare column. If there are multiple minimum values, one is selected at random. |
| Example        | min_by({f1},{f2}), returns: 10 |

## mode

| Syntax         | mode() within group(ORDER BY expr2 \[ DESC ] ) |
|----------------|-----------------------------------------------|
| Parameter Description | expr2: Expression used for sorting, optional types: ANY;DESC: Optional parameter, specifies descending order, default is ascending order |
| Return Type    | ANY                                           |
| Description    | Returns the value that appears most frequently; if there is a tie, returns the first one |
| Example        | For the data \[1, 2, 2, 3, 3], mode() within group(ORDER BY value) returns: 2 (2 and 3 have the same frequency, returns the first 2) |

## nth

| Syntax         | nth(ARG, I)               |
|----------------|--------------------------|
| Parameter Description | 1. Parameter to be calculated, optional type: any type;2. Integer parameter, optional type: number |
| Return Type    | Any type                 |
| Description    | Retrieves the i-th value |
| Example        | nth({f1}, 2), returns: 30 (index starts from 1) |

## percentile

| Syntax         | percentile(ARG, P)        |
|----------------|--------------------------|
| Parameter Description | 1. Numeric type parameter, allowed type: number;2. Decimal between 0 and 1, allowed type: number |
| Return Type    | Number                   |
| Description    | Calculates the percentile |
| Example        | percentile({f1}, 50), returns: 30 |

## regr_avgx

| Syntax         | regr_avgx(ARG1, ARG2)     |
|----------------|---------------------------|
| Parameter Description | 1. ARG1, optional type: number;2. ARG2, optional type: number |
| Return Type    | Number                    |
| Description    | Returns the average value of the independent variable (sum(ARG2)/N) |
| Example        | regr_avgx({f1},{f2}), returns: 46 |

## regr_avgy

| Syntax        | regr_avgy(ARG1, ARG2)         |
|---------------|-------------------------------|
| Parameter Description | 1. ARG1, optional type: number;2. ARG2, optional type: number |
| Return Type   | Number                        |
| Description   | Returns the average value of the dependent variable (sum(ARG1)/N) |
| Example       | regr_avgy({f1},{f2}), returns: 36 |

## regr_count

| Syntax         | regr_count(ARG1, ARG2)    |
|----------------|---------------------------|
| Parameter Description | 1. ARG1, optional type: number;2. ARG2, optional type: number |
| Return Type    | Number                    |
| Description    | The number of input rows where both expressions are not null |
| Example        | regr_count({f1},{f2}), returns: 5 |

## regr_intercept

| Syntax         | regr_intercept(ARG1, ARG2) |
|----------------|----------------------------|
| Parameter Description | 1. ARG1, optional type: number;2. ARG2, optional type: number |
| Return Type    | Number                     |
| Description    | The intercept of the linear equation of the least squares fit determined by the (arg2, arg1) pairs for arg1 |
| Example        | (arg1, arg2) = \[(1,2),(2,3),(3,4),(4,5),(5,6),(1000,1)], regr_intercept(arg1, arg2) returns 665.666666666666 |

## regr_r2

| Syntax        | regr_r2(ARG1, ARG2)       |
|---------------|---------------------------|
| Parameter Description | 1. ARG1, optional type: number;2. ARG2, optional type: number |
| Return Type   | Number                    |
| Description   | Square of the correlation coefficient |
| Example       | (arg1, arg2) = \[(1,2),(2,3),(3,4),(4,5),(5,6),(10,1)], regr_r2({arg1}, {arg2}) = 0.06323185011709602 |

## regr_slope

| Syntax        | regr_slope(ARG1, ARG2)    |
|---------------|---------------------------|
| Parameter Description | 1. ARG1, optional type: number;2. ARG2, optional type: number |
| Return Type   | Number                    |
| Description   | The slope of the least-squares fit linear equation determined by the (arg2, arg1) pairs |
| Example       | (arg1, arg2) = \[(1,2),(2,3),(3,4),(4,5),(5,6),(10,1)], regr_slope(arg1, arg2) = -0.42857142857142855 |

## regr_sxx

| Syntax        | regr_sxx(ARG1, ARG2)      |
|---------------|---------------------------|
| Parameter Description | 1. ARG1, optional type: number;2. ARG2, optional type: number |
| Return Type   | Number                    |
| Description   | sum(arg2^2) - sum(arg2)^2/N (the "sum of squares" of the independent variable) |
| Example       | (arg1, arg2) = \[(1,2),(2,3),(3,4),(4,5),(5,6),(10,1)], regr_sxx(arg1, arg2) = 17.5 |

## regr_sxy

| Syntax        | regr_sxy(ARG1, ARG2)      |
|---------------|--------------------------|
| Parameter Description | 1. ARG1, optional type: number;2. ARG2, optional type: number |
| Return Type   | Number                   |
| Description   | sum(arg2 \* arg1) - sum(arg2) \* sum(arg1)/N (the "sum of products" of the independent variable and the dependent variable) |
| Example       | (arg1, arg2) = \[(1,2),(2,3),(3,4),(4,5),(5,6),(10,1)], regr_sxy(arg1, arg2) = -7.5 |

## regr_syy

| Syntax        | regr_syy(ARG1, ARG2)      |
|---------------|--------------------------|
| Parameter Description | 1. ARG1, optional type: number;2. ARG2, optional type: number |
| Return Type   | Number                   |
| Description   | sum(arg1^2) - sum(arg1)^2/N (the "sum of squares" of the dependent variable) |
| Example       | (arg1, arg2) = \[(1,2),(2,3),(3,4),(4,5),(5,6),(10,1)], regr_syy(arg1, arg2) = 50.833333333333336 |

## set_collect

| Syntax        | set_collect(ARG)          |
|---------------|--------------------------|
| Parameter     | The parameter to be calculated; optional types: any type |
| Return Type   | Array                    |
| Description   | Aggregate function that collects the values of arg into a deduplicated array. (The return format is an array. If you see null displayed, try converting it to a string to view the result.) |
| Example       | set_collect(\[1, 2, 2, 3]), returns: \[1, 2, 3] |

## set_collect_flatten

| Syntax        | set_collect_flatten(ARR)  |
|---------------|--------------------------|
| Parameter     | Array type parameter, optional type: array |
| Return Type   | Array                    |
| Description   | Aggregate function that collects and flattens the parameters of arr into a deduplicated array. (The return format is an array. If you see a null value, try converting it to a string to view it.) |
| Example       | set_collect_flatten(\[\[1, 2], \[2, 3]]) returns: \[1, 2, 3] |

## stddev_pop

| Syntax         | stddev_pop(N)             |
|----------------|--------------------------|
| Parameter      | Numeric parameter, allowed type: number |
| Return Type    | Number                   |
| Description    | Returns the population standard deviation of a numeric column |
| Example        | n = \[(1000), (1001), (1003), (1004), (1005), (1000)], stddev_pop(n) = 1.9507833184532709 |

## stddev_samp

| Syntax         | stddev_samp(N)            |
|----------------|---------------------------|
| Parameter      | Numeric parameter, optional type: number |
| Return Type    | Number                    |
| Description    | Returns the sample standard deviation of a numeric column |
| Example        | n = \[(1000),(1001),(1003),(1004),(1005),(1000)], stddev_samp(n) = 2.1369760566432809 |

## string_agg

| Syntax         | string_agg(S, DELIMITER)  |
|----------------|--------------------------|
| Parameter Description | 1. S: The field to be concatenated, optional types: any type;2. DELIMITER: The delimiter, optional type: string |
| Return Type    | String                   |
| Description    | Concatenates the field S into a string, separated by DELIMITER. For Oracle, the within group clause is required. |
| Example        | string_agg(\['a', 'b', 'c'], ','), returns: 'a,b,c' |

## sum

| Syntax        | sum(N)                    |
|---------------|---------------------------|
| Parameter     | Numeric parameter, allowed type: number |
| Return Type   | Number                    |
| Description   | Returns the sum of the specified numeric column |
| Example       | sum({f1}), returns: 210   |

## var_pop

| Syntax         | var_pop(N)                |
|----------------|--------------------------|
| Parameter      | Numeric parameter, accepted type: number |
| Return Type    | Number                   |
| Description    | Returns the population variance of a numeric column |
| Example        | n = \[(1000), (1001), (1003), (1004), (1005), (1000)], var_pop(n) = 3.8055555555555554 (Note: The original example description was incorrect; this is the actual population variance) |

## var_samp

| Syntax        | var_samp(N)               |
|---------------|--------------------------|
| Parameter     | Numeric parameter, optional type: number |
| Return Type   | Number                   |
| Description   | Returns the sample variance of a numeric column (the square of the sample standard deviation) |
| Example       | n = \[(1000), (1001), (1003), (1004), (1005), (1000)], var_samp(n) = 4.5666666666666667 |

## Adding Filter Conditions

It is supported to use the filter clause after an aggregate calculation expression to achieve filtering effects. The syntax is as follows, where filter_clause is the filter condition:

```
aggregate_name(expression ...) FILTER (WHERE filter_clause )
```

* Example 1:

```
sum({f1}) FILTER (WHERE {f1} > 10)
```

This means summing the data where the f1 field is greater than 10.

* Example 2:

```
avg({f1}) FILTER (WHERE {f1} > 10 AND {f2} = 'abc')
```

This means averaging the data where the f1 field is greater than 10 and the f2 field equals 'abc'.

## Adding Sorting Clauses

Adding sorting clauses allows you to sort the aggregation results. The functions that support sorting clauses are: first, last, mode, percentile, string_agg. The syntax is as follows, where order_clause is the sorting condition.

```
aggregate_name(expression ...) within group(ORDER BY order_clause)
```

* Example 1:

```
first({f1}) within group(ORDER BY {f2})
```

This means sorting the f1 field by the f2 field and taking the first value of the f1 field in each group.

* Example 2:

```
last({f1}) within group(ORDER BY {f2})
```

This means sorting the f1 field by the f2 field and taking the last value of the f1 field in each group.

* Example 3:

```
percentile({f1}, 0.5) within group(ORDER BY {f2})
```

This means sorting the f1 field by the f2 field and taking the value of the f1 field at the 50% position in each group.