# Aggregation Functions

Aggregate functions return a single result for a group of rows. The pages below provide detailed signatures, usage examples, and notes for each function.

## Basic Aggregations

| Function                                                                                  | Description                               |
| ----------------------------------------------------------------------------------------- | ----------------------------------------- |
| [COUNT](/functions/aggregation/count.md)                                                  | Count of rows                             |
| [SUM](/functions/aggregation/sum.md) / [SUMMV](/functions/aggregation/summv.md)           | Sum of values                             |
| [MIN](/functions/aggregation/min.md) / [MINMV](/functions/aggregation/minmv.md)           | Minimum value                             |
| [MAX](/functions/aggregation/max.md) / [MAXMV](/functions/aggregation/maxmv.md)           | Maximum value                             |
| [AVG](/functions/aggregation/avg.md) / [AVGMV](/functions/aggregation/avgmv.md)           | Average of values                         |
| [MODE](/functions/aggregation/mode.md)                                                    | Most frequent value                       |
| [HISTOGRAM](/functions/aggregation/histogram.md)                                          | Histogram of values                       |
| [SUMPRECISION](/functions/aggregation/sumprecision.md)                                    | High-precision sum using BigDecimal       |
| [ANYVALUE](/functions/aggregation/anyvalue.md)                                            | Any arbitrary non-null value from a group |
| [BOOLAND](/functions/aggregation/booland.md) / [BOOLOR](/functions/aggregation/boolor.md) | Logical AND/OR across boolean values      |

## Array and String Aggregations

| Function                                                   | Description                                |
| ---------------------------------------------------------- | ------------------------------------------ |
| [ARRAYAGG](/functions/aggregation/arrayagg.md)             | Collect values into an array               |
| [LISTAGG](/functions/aggregation/listagg.md)               | Concatenate values into a delimited string |
| [SUMARRAYLONG](/functions/aggregation/sumarraylong.md)     | Element-wise sum of long arrays            |
| [SUMARRAYDOUBLE](/functions/aggregation/sumarraydouble.md) | Element-wise sum of double arrays          |

## Statistical Functions

| Function                                                                                                    | Description                                 |
| ----------------------------------------------------------------------------------------------------------- | ------------------------------------------- |
| [SKEWNESS](/functions/aggregation/skewness.md)                                                              | Skewness of a distribution                  |
| [KURTOSIS](/functions/aggregation/kurtosis.md)                                                              | Kurtosis of a distribution                  |
| [DISTINCTSUM](/functions/aggregation/distinctsum.md) / [DISTINCTAVG](/functions/aggregation/distinctavg.md) | Sum/average of distinct values              |
| [EXPRMIN](/functions/aggregation/exprmin.md) / [EXPRMAX](/functions/aggregation/exprmax.md)                 | Project columns at row with min/max measure |
| [ARG\_MIN / ARG\_MAX](/functions/aggregation/arg_min-arg_max.md)                                            | Project column at row with min/max measure  |

## Set and Sketch Functions

| Function                                 | Description                            |
| ---------------------------------------- | -------------------------------------- |
| [IDSET](/functions/aggregation/idset.md) | Build an IdSet for efficient filtering |

For sketch-based functions (FrequentItems, CPC, HLL+, ULL, Tuple), see [Sketch Functions](/functions/sketch.md). For funnel analysis functions, see [Funnel Functions](/functions/funnel.md).

## Distinct Counting

| Function                                                                       | Description                          |
| ------------------------------------------------------------------------------ | ------------------------------------ |
| [DISTINCT](/functions/aggregation/distinct.md)                                 | Exact distinct values                |
| [DISTINCTCOUNT](/functions/aggregation/distinctcount.md)                       | Exact distinct count                 |
| [DISTINCTCOUNTBITMAP](/functions/aggregation/distinctcountbitmap.md)           | Distinct count using bitmap          |
| [DISTINCTCOUNTHLL](/functions/aggregation/distinctcounthll.md)                 | Approximate distinct count using HLL |
| [DISTINCTCOUNTTHETASKETCH](/functions/aggregation/distinctcountthetasketch.md) | Distinct count using Theta sketch    |
| [DISTINCTCOUNTSMARTHLL](/functions/aggregation/distinctcountsmarthll.md)       | Hybrid exact/HLL distinct count      |

For sketch-based distinct count functions (CPC, HLL+, ULL, Tuple), see [Sketch Functions](/functions/sketch.md).

## Percentile Functions

| Function                                                         | Description                 |
| ---------------------------------------------------------------- | --------------------------- |
| [PERCENTILE](/functions/aggregation/percentile.md)               | Exact percentile            |
| [PERCENTILEEST](/functions/aggregation/percentileest.md)         | Estimated percentile        |
| [PERCENTILEKLL](/functions/aggregation/percentilekll.md)         | Percentile using KLL sketch |
| [PERCENTILETDIGEST](/functions/aggregation/percentiletdigest.md) | Percentile using T-Digest   |

## Window-Related Functions

| Function                                                                                                          | Description                      |
| ----------------------------------------------------------------------------------------------------------------- | -------------------------------- |
| [FIRST\_VALUE](/functions/aggregation/first_value.md) / [LAST\_VALUE](/functions/aggregation/last_value.md)       | First/last value in window       |
| [LEAD](/functions/aggregation/lead.md) / [LAG](/functions/aggregation/lag.md)                                     | Access subsequent/preceding rows |
| [FIRSTWITHTIME](/functions/aggregation/firstwithtime.md) / [LASTWITHTIME](/functions/aggregation/lastwithtime.md) | First/last value by time         |

## Multi-Value Column Functions

The following aggregation functions can be used for multi-value columns:

| Function                                                                 | Description                                                                |
| ------------------------------------------------------------------------ | -------------------------------------------------------------------------- |
| [COUNTMV](/functions/aggregation/countmv.md)                             | Returns the count of a multi-value column                                  |
| [MINMV](/functions/aggregation/minmv.md)                                 | Returns the minimum value of a numeric multi-value column                  |
| [MAXMV](/functions/aggregation/maxmv.md)                                 | Returns the maximum value of a numeric multi-value column                  |
| [SUMMV](/functions/aggregation/summv.md)                                 | Returns the sum of the values for a numeric multi-value column             |
| [AVGMV](/functions/aggregation/avgmv.md)                                 | Returns the average of the values for a numeric multi-value column         |
| [MINMAXRANGEMV](/functions/aggregation/minmaxrangemv.md)                 | Returns the `max - min` value for a numeric multi-value column             |
| [PERCENTILEMV](/functions/aggregation/percentilemv.md)                   | Returns the Nth percentile of a numeric multi-value column                 |
| [PERCENTILEESTMV](/functions/aggregation/percentileestmv.md)             | Returns the Nth percentile using Quantile Digest for a multi-value column  |
| [PERCENTILETDIGESTMV](/functions/aggregation/percentiletdigestmv.md)     | Returns the Nth percentile using T-Digest for a multi-value column         |
| [DISTINCTCOUNTMV](/functions/aggregation/distinctcountmv.md)             | Returns the count of distinct values for a multi-value column              |
| [DISTINCTCOUNTBITMAPMV](/functions/aggregation/distinctcountbitmapmv.md) | Returns the count of distinct values using bitmap for a multi-value column |
| [DISTINCTCOUNTHLLMV](/functions/aggregation/distinctcounthllmv.md)       | Returns an approximate distinct count using HLL for a multi-value column   |
| [DISTINCTCOUNTRAWHLLMV](/functions/aggregation/distinctcountrawhllmv.md) | Returns HyperLogLog response serialized as string for a multi-value column |
| [DISTINCTSUMMV](/functions/aggregation/distinctsummv.md)                 | Returns the sum of distinct values of a numeric multi-value column         |
| [DISTINCTAVGMV](/functions/aggregation/distinctavgmv.md)                 | Returns the average of distinct values of a numeric multi-value column     |

For sketch-based multi-value functions (HLL+), see [Sketch Functions](/functions/sketch.md).

## FILTER Clause in Aggregation

Pinot supports the FILTER clause in aggregation queries:

```sql
SELECT SUM(COL1) FILTER (WHERE COL2 > 300),
       AVG(COL2) FILTER (WHERE COL2 < 50)
FROM MyTable WHERE COL3 > 50
```

In the query above, `COL1` is aggregated only for rows where `COL2 > 300 and COL3 > 50`. Similarly, `COL2` is aggregated where `COL2 < 50 and COL3 > 50`.

With [NULL Value Support](/build-with-pinot/querying-and-sql/sql-syntax/null-value-support.md) enabled, this allows filtering out null values while performing aggregation:

```sql
SELECT SUM(COL1) FILTER (WHERE COL1 IS NOT NULL)
FROM MyTable WHERE COL3 > 50
```

## Deprecated Functions

| Function  | Description                                                                                                                                  |
| --------- | -------------------------------------------------------------------------------------------------------------------------------------------- |
| FASTHLL   | Stores serialized HyperLogLog in String format. Performs worse than DISTINCTCOUNTHLL, which supports serialized HyperLogLog in BYTES format. |
| FASTHLLMV | Multi-value version of FASTHLL. Also deprecated in favor of DISTINCTCOUNTHLL.                                                                |

## Additional Reference Pages

| Function                                                                        | Function                                                                                     |
| ------------------------------------------------------------------------------- | -------------------------------------------------------------------------------------------- |
| [DISTINCT\_COUNT\_OFF\_HEAP](/functions/aggregation/distinct_count_off_heap.md) | [DISTINCTCOUNTHLLPLUS](/functions/aggregation/distinctcounthll-1.md)                         |
| [DISTINCTCOUNTRAWHLL](/functions/aggregation/distinctcountrawhll.md)            | [DISTINCTCOUNTRAWTHETASKETCH](/functions/aggregation/distinctcountrawthetasketch.md)         |
| [DISTINCTCOUNTULL](/functions/aggregation/distinctcountull.md)                  | [FUNNELEVENTSFUNCTIONEVAL](/functions/aggregation/funneleventsfunctioneval.md)               |
| [FUNNELSTEPDURATIONSTATS](/functions/aggregation/funnelstepdurationstats.md)    | [maxString](/functions/aggregation/maxstring.md)                                             |
| [minmaxrange](/functions/aggregation/minmaxrange.md)                            | [minString](/functions/aggregation/minstring.md)                                             |
| [percentilekllmv](/functions/aggregation/percentilekllmv.md)                    | [percentilerawkll](/functions/aggregation/percentilerawkll.md)                               |
| [percentilerawkllmv](/functions/aggregation/percentilerawkllmv.md)              | [SEGMENTPARTITIONEDDISTINCTCOUNT](/functions/aggregation/segmentpartitioneddistinctcount.md) |
| [VALUEIN](/functions/aggregation/valuein.md)                                    |                                                                                              |


---

# 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.pinot.apache.org/functions/aggregation.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.