# Aggregate

## Index

[General Aggregate Function](#general-aggregate-functions)

* [AVG](#avg)
* [MAX](#max)
* [MIN](#min)
* [SUM](#sum)
* [EVERY](#every)
* [ANY](#any)
* [COUNT](#count)
* [STDDEV\_POP](#stddev_pop)
* [STDDEV\_SAMP](#stddev_samp)
* [VAR\_POP](#var_pop)
* [VAR\_SAMP](#var_samp)
* [BIT\_AND*\_*AGG](#bit_and_agg)
* [BIT\_OR\_AGG](#bit_or_agg)
* [BIT\_XOR\_AGG](#bit_xor_agg)
* [BIT\_NAND\_AGG](#bit_nand_agg)
* [BIT\_NOR\_AGG](#bit_nor_agg)
* [BIT\_XNOR\_AGG](#bit_xnor_agg)
* [ENVELOPE](#envelope)

[Binary Set Functions](#binary-set-functions)

* [COVAR\_POP](#covar_pop)
* [COVAR\_SAMP](#covar_samp)
* [CORR](#corr)
* [REGR\_SLOPE](#regr_slope)
* [REGR\_INTERCEPT](#regr_intercept)
* [REGR\_COUNT](#regr_count)
* [REGR\_R2](#regr_r2)
* [REGR\_AVGX](#regr_avgx)
* [REGR\_AVGY](#regr_avgy)
* [REGR\_SXX](#regr_sxx)
* [REGR\_SYY](#regr_syy)
* [REGR\_SXY](#regr_sxy)

[Ordered Aggregate Functions](#ordered-aggregate-functions)

* [LISTAGG](#listagg)
* [ARRAY\_AGG](#json_arrayagg)

[Hypothetical Set Functions](#hypothetical-set-functions)

* [RANK aggregate](#rank-aggregate)
* [DENSE\_RANK aggregate](#dense_rank-aggregate)
* [PERCENT\_RANK aggregate](#percent_rank-aggregate)
* [CUME\_DIST aggregate](#cume_dist-aggregate)

[Inverse Distribution Functions](#inverse-distribution-functions)

* [PERCENTILE\_COUNT](#percentile_cont)
* [PERCENTILE\_DISC](#percentile_disc)
* [MEDIAN](#median)
* [MODE](#mode)

[JSON Aggregate Functions](#json-aggregate-functions)

* [JSON\_OBJECTAGG](#json_objecagg)
* [JSON\_ARRAYAGG](#json_arrayagg)

## Details

### General Aggregate Functions

#### AVG

{% hint style="info" %}

```sql
AVG ( [ DISTINCT|ALL ] { numeric | interval } )
[FILTER (WHERE expression)] [OVER windowNameOrSpecification]
```

{% endhint %}

Return average (mean) value. If no rows are selected, the result is **`NULL`**. Aggregation are only allowed in select statements.

The data type of result is **`DOUBLE PRECISION`** for **`TINYINT`**, **`SMALLINT`**, **`INTEGER`**, and **`REAL`** arguments, **`NUMERIC`** with additional 10 decimal digits of precision and scale for **`BIGINT`** and **`NUMERIC`** arguments; **`DECFLOAT`** with additional 10 decimal digits of precision for **`DOUBLE PRECISION`** and **`DECFLOAT`** arguments; **`INTERVAL`** with the same leading field precision, all additional smaller datetime units in interval qualifier, and the maximum scale for **`INTERVAL`** arguments.

Examples:

```sql
AVG(X)
```

#### MAX

{% hint style="info" %}
{% code overflow="wrap" %}

```sql
MAX(value)
[FILTER (WHERE expression)] [OVER windowNameOrSpecification]
```

{% endcode %}
{% endhint %}

The highest value. If no rows are selected, the result is **`NULL`**. Aggregate are only allowed in 

select statements. The returned value is of the same data type are the parameter.

Examples:

```sql
MAX(X)
```

#### MIN

{% hint style="info" %}
{% code overflow="wrap" %}

```sql
MIN(value)
[FILTER (WHERE expression)] [OVER windowNameOrSpecification]
```

{% endcode %}
{% endhint %}

The lowest value. If no rows are selected, the result is **`NULL`**. Aggregates are only allowed in select statements. The returned value is of the same data type as the parameter.

Examples:

```sql
MIN(X)
```

#### SUM

{% hint style="info" %}

```sql
SUM( [ DISTINCT|ALL ] { numeric | interval | { boolean } } )
[FILTER (WHERE expression)] [OVER windowNameOrSpecification]
```

{% endhint %}

The sum of all values. If no rows are selected, the result is **`NULL`**.Aggregates are only allowed in select statements.

Examples:

```sql
SUM(X)
```

#### EVERY

{% hint style="info" %}

```sql
{EVERY| {BOOL_AND}}(boolean)
[FILTER (WHERE expression)] [OVER windowNameOrSpecification]
```

{% endhint %}

Returns true if all expressions are true. If no rows are selected, the result is **`NULL`**. Aggregates are only allowed in select statements.

Examples:

```sql
EVERY(ID>10)
```

#### ANY

{% hint style="info" %}

```sql
AVG ( [ DISTINCT|ALL ] { numeric | interval } )
[FILTER (WHERE expression)] [OVER windowNameOrSpecification]
```

{% endhint %}

Returns true if any expression is true. If no rows are selected, the result is **`NULL`**. Aggregates are only allowed in select statements.

Note that if **`ANY`** or **`SOME`** aggregate function is placed on the right side of comparison operation or distinct predicate and argument of this function is a subquery additional parentheses around aggregate function are required, otherwise it will be parsed as quantified predicate.

Examples:

```sql
ANY(NAME LIKE 'W%')
A = (ANY((SELECT B FROM C)))
```

#### COUNT

{% hint style="info" %}

```sql
AVG ( [ DISTINCT|ALL ] { numeric | interval } )
[FILTER (WHERE expression)] [OVER windowNameOrSpecification]
```

{% endhint %}

TODO

#### STDDEV\_POP

{% hint style="info" %}

```sql
AVG ( [ DISTINCT|ALL ] { numeric | interval } )
[FILTER (WHERE expression)] [OVER windowNameOrSpecification]
```

{% endhint %}

TODO

#### STDDEV\_SAMP

{% hint style="info" %}

```sql
AVG ( [ DISTINCT|ALL ] { numeric | interval } )
[FILTER (WHERE expression)] [OVER windowNameOrSpecification]
```

{% endhint %}

TODO

#### VAR\_POP

{% hint style="info" %}

```sql
AVG ( [ DISTINCT|ALL ] { numeric | interval } )
[FILTER (WHERE expression)] [OVER windowNameOrSpecification]
```

{% endhint %}

TODO

#### VAR\_SAMP

{% hint style="info" %}

```sql
AVG ( [ DISTINCT|ALL ] { numeric | interval } )
[FILTER (WHERE expression)] [OVER windowNameOrSpecification]
```

{% endhint %}

TODO

#### BIT\_AND\_AGG

{% hint style="info" %}

```sql
AVG ( [ DISTINCT|ALL ] { numeric | interval } )
[FILTER (WHERE expression)] [OVER windowNameOrSpecification]
```

{% endhint %}

TODO

#### BIT\_OR\_AGG

{% hint style="info" %}

```sql
AVG ( [ DISTINCT|ALL ] { numeric | interval } )
[FILTER (WHERE expression)] [OVER windowNameOrSpecification]
```

{% endhint %}

TODO

#### BIT\_XOR\_AGG

{% hint style="info" %}

```sql
AVG ( [ DISTINCT|ALL ] { numeric | interval } )
[FILTER (WHERE expression)] [OVER windowNameOrSpecification]
```

{% endhint %}

TODO

#### BIT\_NAND\_AGG

{% hint style="info" %}

```sql
AVG ( [ DISTINCT|ALL ] { numeric | interval } )
[FILTER (WHERE expression)] [OVER windowNameOrSpecification]
```

{% endhint %}

TODO

#### BIT\_NOR\_AGG

{% hint style="info" %}

```sql
AVG ( [ DISTINCT|ALL ] { numeric | interval } )
[FILTER (WHERE expression)] [OVER windowNameOrSpecification]
```

{% endhint %}

TODO

#### BIT\_XNOR\_AGG

{% hint style="info" %}

```sql
AVG ( [ DISTINCT|ALL ] { numeric | interval } )
[FILTER (WHERE expression)] [OVER windowNameOrSpecification]
```

{% endhint %}

TODO

#### ENVELOPE

{% hint style="info" %}

```sql
AVG ( [ DISTINCT|ALL ] { numeric | interval } )
[FILTER (WHERE expression)] [OVER windowNameOrSpecification]
```

{% endhint %}

TODO

### Binary Set Functions

#### COVAR\_POP

{% hint style="info" %}

```sql
AVG ( [ DISTINCT|ALL ] { numeric | interval } )
[FILTER (WHERE expression)] [OVER windowNameOrSpecification]
```

{% endhint %}

TODO

#### COVAR\_SAMP

{% hint style="info" %}

```sql
AVG ( [ DISTINCT|ALL ] { numeric | interval } )
[FILTER (WHERE expression)] [OVER windowNameOrSpecification]
```

{% endhint %}

TODO

#### CORR

{% hint style="info" %}

```sql
AVG ( [ DISTINCT|ALL ] { numeric | interval } )
[FILTER (WHERE expression)] [OVER windowNameOrSpecification]
```

{% endhint %}

TODO

#### REGR\_SLOPE

{% hint style="info" %}

```sql
AVG ( [ DISTINCT|ALL ] { numeric | interval } )
[FILTER (WHERE expression)] [OVER windowNameOrSpecification]
```

{% endhint %}

TODO

#### REGR\_INTERCEPT

{% hint style="info" %}

```sql
AVG ( [ DISTINCT|ALL ] { numeric | interval } )
[FILTER (WHERE expression)] [OVER windowNameOrSpecification]
```

{% endhint %}

TODO

#### REGR\_COUNT

{% hint style="info" %}

```sql
AVG ( [ DISTINCT|ALL ] { numeric | interval } )
[FILTER (WHERE expression)] [OVER windowNameOrSpecification]
```

{% endhint %}

TODO

#### REGR\_R2

{% hint style="info" %}

```sql
AVG ( [ DISTINCT|ALL ] { numeric | interval } )
[FILTER (WHERE expression)] [OVER windowNameOrSpecification]
```

{% endhint %}

TODO

#### REGR\_AVGX

{% hint style="info" %}

```sql
AVG ( [ DISTINCT|ALL ] { numeric | interval } )
[FILTER (WHERE expression)] [OVER windowNameOrSpecification]
```

{% endhint %}

TODO

#### REGR\_AVGY

{% hint style="info" %}

```sql
AVG ( [ DISTINCT|ALL ] { numeric | interval } )
[FILTER (WHERE expression)] [OVER windowNameOrSpecification]
```

{% endhint %}

TODO

#### REGR\_SXX

{% hint style="info" %}

```sql
AVG ( [ DISTINCT|ALL ] { numeric | interval } )
[FILTER (WHERE expression)] [OVER windowNameOrSpecification]
```

{% endhint %}

TODO

#### REGR\_SYY

{% hint style="info" %}

```sql
AVG ( [ DISTINCT|ALL ] { numeric | interval } )
[FILTER (WHERE expression)] [OVER windowNameOrSpecification]
```

{% endhint %}

TODO

#### REGR\_SXY

{% hint style="info" %}

```sql
AVG ( [ DISTINCT|ALL ] { numeric | interval } )
[FILTER (WHERE expression)] [OVER windowNameOrSpecification]
```

{% endhint %}

TODO

### Ordered Aggregate Functions

#### LISTAGG

{% hint style="info" %}

```sql
AVG ( [ DISTINCT|ALL ] { numeric | interval } )
[FILTER (WHERE expression)] [OVER windowNameOrSpecification]
```

{% endhint %}

TODO

#### ARRAY\_AGG

{% hint style="info" %}

```sql
AVG ( [ DISTINCT|ALL ] { numeric | interval } )
[FILTER (WHERE expression)] [OVER windowNameOrSpecification]
```

{% endhint %}

TODO

### Hypothetical Set Functions

#### RANK aggregate

{% hint style="info" %}

```sql
AVG ( [ DISTINCT|ALL ] { numeric | interval } )
[FILTER (WHERE expression)] [OVER windowNameOrSpecification]
```

{% endhint %}

TODO

#### DENSE\_RANK aggregate

{% hint style="info" %}

```sql
AVG ( [ DISTINCT|ALL ] { numeric | interval } )
[FILTER (WHERE expression)] [OVER windowNameOrSpecification]
```

{% endhint %}

TODO

#### PERCENT\_RANK aggregate

{% hint style="info" %}

```sql
AVG ( [ DISTINCT|ALL ] { numeric | interval } )
[FILTER (WHERE expression)] [OVER windowNameOrSpecification]
```

{% endhint %}

TODO

#### CUME\_DIST aggregate

{% hint style="info" %}

```sql
AVG ( [ DISTINCT|ALL ] { numeric | interval } )
[FILTER (WHERE expression)] [OVER windowNameOrSpecification]
```

{% endhint %}

TODO

### Inverse Distribution Functions

#### PERCENTILE\_CONT

{% hint style="info" %}

```sql
AVG ( [ DISTINCT|ALL ] { numeric | interval } )
[FILTER (WHERE expression)] [OVER windowNameOrSpecification]
```

{% endhint %}

TODO

#### PERCENTILE\_DISC

{% hint style="info" %}

```sql
AVG ( [ DISTINCT|ALL ] { numeric | interval } )
[FILTER (WHERE expression)] [OVER windowNameOrSpecification]
```

{% endhint %}

TODO

#### MEDIAN

{% hint style="info" %}

```sql
AVG ( [ DISTINCT|ALL ] { numeric | interval } )
[FILTER (WHERE expression)] [OVER windowNameOrSpecification]
```

{% endhint %}

TODO

#### MODE

{% hint style="info" %}

```sql
AVG ( [ DISTINCT|ALL ] { numeric | interval } )
[FILTER (WHERE expression)] [OVER windowNameOrSpecification]
```

{% endhint %}

TODO

### JSON Aggregate Functions

#### JSON\_OBJECAGG

{% hint style="info" %}

```sql
AVG ( [ DISTINCT|ALL ] { numeric | interval } )
[FILTER (WHERE expression)] [OVER windowNameOrSpecification]
```

{% endhint %}

TODO

#### JSON\_ARRAYAGG

{% hint style="info" %}

```sql
AVG ( [ DISTINCT|ALL ] { numeric | interval } )
[FILTER (WHERE expression)] [OVER windowNameOrSpecification]
```

{% endhint %}

TODO