# Aggregate Functions

#### <mark style="color:purple;">AVG(</mark>  \<expr>  <mark style="color:purple;">)</mark> &#x20;

Returns the arithmetic mean of all input values.

```sql
select avg(colA) from table1;
```

#### <mark style="color:purple;">COUNT(</mark>  \<expr>  <mark style="color:purple;">)</mark>&#x20;

Returns the number of rows in an input value.

```sql
select count(colA) from table1;

select count(*), colA from table1 group by colA;
```

#### <mark style="color:purple;">DISTINCT(</mark>  \<expr>  <mark style="color:purple;">)</mark>

Returns the number of distinct values in a given column.

```sql
select distinct(colA) from table1;
```

<mark style="color:purple;">**MAX**</mark><mark style="color:purple;">(</mark>  \<expr>  <mark style="color:purple;">)</mark>

Returns the maximum value of all input values.

```sql
select max(colA) from table1;
```

#### <mark style="color:purple;">MIN(</mark>  \<expr>  <mark style="color:purple;">)</mark>

Returns the minimum value of all input values.

```sql
select min(colA) from table1;
```

#### <mark style="color:purple;">SUM(</mark>  \<expr>  <mark style="color:purple;">)</mark>

Returns the arithmetic sum of all input values.

```sql
select sum(colA) from table1;
```

#### <mark style="color:purple;">ARBITRARY / ANY\_VALUE(</mark>  \<expr>  <mark style="color:purple;">)</mark>

Returns random value from set of values

```sql
select arbitrary(colA) from table1;
```

#### <mark style="color:purple;">LISTAGG(</mark> \[distinct] \<column> \[, \<delimiter>]) \[within group (order by \<column> ) <mark style="color:purple;">)</mark>

Concatenates the input values together, with each value separated by the specified delimiter string.

Support datatype: String or data type that is cast to string

```sql
select c1, listagg(distinct c2,'++') within group(order by c2)
from(
SELECT c1, c2
from (values (1, 'one'), (1, 'two'),(1, 'three'),(1, 'two'),(1, null),(1, null),(2,null),(2,'five')) AS v1 (c1, c2))
group by 1
order by 1,2;  
 > |1    |one++three++two
   |2    |five
```

**Usages**:

1. This function returns a single string comprising all non-NULL input values
2. If we didn’t provide WITHIN GROUP (ORDER BY) then the result will be unpredictable
3. We support exact column name in the order by of `within group(order by <column>)`we dont support ordinals in the order by
4. We don’t support `over()` clause with listagg.
5. If delimiter is not provided then default value is `,`.

#### <mark style="color:purple;">STRING\_AGG(</mark> \[distinct] \<column> \[, \<delimiter>]) order by \<column>  limit \<value>  <mark style="color:purple;">)</mark>

Concatenates the input values together, with each value separated by the specified delimiter string.

Support datatype: String or data type that is cast to string

```sql
select c1, string_agg(distinct c2,',' order by c2 limit 2) as c1
from(
select c1, c2
from (values (1, 'one'), (1, 'two'),(1, 'three'),(1, 'two'),(1, null),(1, null),(2,null),(2,'five')) AS v1 (c1, c2))
group by 1
order by 1,2;
> |1    |one ,three
  |2    |five
```

**Usages**:

1. This function returns a single string comprising all non-NULL input values
2. If we didn’t provide `ORDER BY <column>` then the result will be unpredictable
3. We support exact column name in the order by of `ORDER BY <column>`we don't support ordinals in the order by
4. We don’t support `over()` clause with string.
5. If delimiter is not provided then default value is `-`.

#### <mark style="color:purple;">COUNT(DISTINCT(</mark>  \<expr>  <mark style="color:purple;">))</mark>

Returns distinct count of first projection used in the query

```sql
select count(distinct colA, colB) from table1;
```