# 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;
```


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://docs.e6data.com/product-documentation/sql-command-reference/aggregate-functions.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.