# Aggregation Functions Aggregate functions return a single result for a group of rows. The following table shows supported aggregate functions in Pinot. | Function | Description | Example | Default Value When No Record Selected | | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | ---------------------------------------------------------------------------------------- | |

ARG\_MIN

/ARG\_MAX

| Project a column where the maxima appears in a series of measuring columns. | ARG\_MAX(measuring1, measuring2, measuring3, projection) | Will return no result | | [**AVGVALUEINTEGERSUMTUPLESKETCH**](https://github.com/pinot-contrib/pinot-docs/blob/latest/configuration-reference/functions/avgvalueintegersumtuplesketch.md) | See [Cardinality Estimation](https://docs.pinot.apache.org/release-1.1.0/for-users/user-guide-query/query-syntax/how-to-handle-unique-counting) | | `0` | | [**COUNT**](https://docs.pinot.apache.org/release-1.1.0/configuration-reference/functions/count) | Returns the count of the records as `Long` | `COUNT(*)` | `0` | | [**COVAR\_POP**](https://docs.pinot.apache.org/release-1.1.0/configuration-reference/functions/covar_pop) | Returns the population covariance between of 2 numerical columns as `Double` | `COVAR_POP(col1, col2)` | `Double.NEGATIVE_INFINITY` | | [**COVAR\_SAMP**](https://docs.pinot.apache.org/release-1.1.0/configuration-reference/functions/covar_samp) | Returns the sample covariance between of 2 numerical columns as `Double` | `COVAR_SAMP(col1, col2)` | `Double.NEGATIVE_INFINITY` | | [**HISTOGRAM**](https://docs.pinot.apache.org/release-1.1.0/configuration-reference/functions/histogram) | Calculate the histogram of a numeric column as `Double[]` | `HISTOGRAM(numberOfGames,0,200,10)` | `0, 0, ..., 0` | | [**MIN**](https://docs.pinot.apache.org/release-1.1.0/configuration-reference/functions/min) | Returns the minimum value of a numeric column as `Double` | `MIN(playerScore)` | `Double.POSITIVE_INFINITY` | | [**MAX**](https://docs.pinot.apache.org/release-1.1.0/configuration-reference/functions/max) | Returns the maximum value of a numeric column as `Double` | `MAX(playerScore)` | `Double.NEGATIVE_INFINITY` | | [**SUM**](https://docs.pinot.apache.org/release-1.1.0/configuration-reference/functions/sum) | Returns the sum of the values for a numeric column as `Double` | `SUM(playerScore)` | `0` | | [**SUMPRECISION**](https://github.com/pinot-contrib/pinot-docs/blob/master/configuration-reference/functions/sumprecision.md) | Returns the sum of the values for a numeric column with optional precision and scale as `BigDecimal` | `SUMPRECISION(salary), SUMPRECISION(salary, precision, scale)` | `0.0` | | [**AVG**](https://github.com/pinot-contrib/pinot-docs/blob/master/configuration-reference/functions/avg.md) | Returns the average of the values for a numeric column as `Double` | `AVG(playerScore)` | `Double.NEGATIVE_INFINITY` | | [**MODE**](https://docs.pinot.apache.org/release-1.1.0/configuration-reference/functions/mode) | Returns the most frequent value of a numeric column as `Double`. When multiple modes are present it gives the minimum of all the modes. This behavior can be overridden to get the maximum or the average mode. |

MODE(playerScore)

MODE(playerScore, 'MIN')

MODE(playerScore, 'MAX')

MODE(playerScore, 'AVG')

| `Double.NEGATIVE_INFINITY` | | [**MINMAXRANGE**](https://docs.pinot.apache.org/release-1.1.0/configuration-reference/functions/minmaxrange) | Returns the `max - min` value for a numeric column as `Double` | `MINMAXRANGE(playerScore)` | `Double.NEGATIVE_INFINITY` | | [**PERCENTILE(column, N)**](https://docs.pinot.apache.org/release-1.1.0/configuration-reference/functions/percentile) | Returns the Nth percentile of the values for a numeric column as `Double`. N is a decimal number between 0 and 100 inclusive. | `PERCENTILE(playerScore, 50) PERCENTILE(playerScore, 99.9)` | `Double.NEGATIVE_INFINITY` | | [**PERCENTILEEST(column, N)**](https://docs.pinot.apache.org/release-1.1.0/configuration-reference/functions/percentileest) | Returns the Nth percentile of the values for a numeric column using [Quantile Digest](https://github.com/airlift/airlift/blob/master/stats/src/main/java/io/airlift/stats/QuantileDigest.java) as `Long` |

PERCENTILEEST(playerScore, 50)

PERCENTILEEST(playerScore, 99.9)

| `Long.MIN_VALUE` | | [**PERCENTILETDIGEST(column, N)**](https://docs.pinot.apache.org/release-1.1.0/configuration-reference/functions/percentiletdigest) | Returns the Nth percentile of the values for a numeric column using [T-digest](https://raw.githubusercontent.com/tdunning/t-digest/master/docs/t-digest-paper/histo.pdf) as `Double` |

PERCENTILETDIGEST(playerScore, 50)

PERCENTILETDIGEST(playerScore, 99.9)

| `Double.NaN` | | [**PERCENTILETDIGEST(column, N, CF)**](https://docs.pinot.apache.org/release-1.1.0/configuration-reference/functions/percentiletdigest) | Returns the Nth percentile (using compression factor of CF) of the values for a numeric column using [T-digest](https://raw.githubusercontent.com/tdunning/t-digest/master/docs/t-digest-paper/histo.pdf) as `Double` |

PERCENTILETDIGEST(playerScore, 50, 1000)

PERCENTILETDIGEST(playerScore, 99.9, 500)

| `Double.NaN` | | PERCENTILESMARTTDIGEST | Returns the Nth percentile of the values for a numeric column as `Double`. When there are too many values, automatically switch to approximate percentile using *TDigest*. The switch `threshold` (100\_000 by default) and `compression` (100 by default) for the *TDigest* can be configured via the optional second argument. |

PERCENTILESMARTTDIGEST(playerScore, 50)

PERCENTILESMARTTDIGEST(playerScore, 99.9, 'threshold=100;compression=50)

| `Double.NEGATIVE_INFINITY` | | [**DISTINCTCOUNT**](https://docs.pinot.apache.org/release-1.1.0/configuration-reference/functions/distinctcount) | Returns the count of distinct values of a column as `Integer` | `DISTINCTCOUNT(playerName)` | `0` | | [**DISTINCTCOUNTBITMAP**](https://docs.pinot.apache.org/release-1.1.0/configuration-reference/functions/distinctcountbitmap) | Returns the count of distinct values of a column as `Integer`. This function is accurate for *INT* column, but approximate for other cases where hash codes are used in distinct counting and there may be hash collisions. | `DISTINCTCOUNTBITMAP(playerName)` | `0` | | [**DISTINCTCOUNTHLL**](https://docs.pinot.apache.org/release-1.1.0/configuration-reference/functions/distinctcounthll) | Returns an approximate distinct count using *HyperLogLog as* `Long`. It also takes an optional second argument to configure the `log2m` for the *HyperLogLog*. | `DISTINCTCOUNTHLL(playerName, 12)` | `0` | | [**DISTINCTCOUNTRAWHLL**](https://docs.pinot.apache.org/release-1.1.0/configuration-reference/functions/distinctcountrawhll) | Returns *HyperLogLog* response serialized as `String`. The serialized HLL can be converted back into an HLL and then aggregated with other HLLs. A common use case may be to merge HLL responses from different Pinot tables, or to allow aggregation after client-side batching. | `DISTINCTCOUNTRAWHLL(playerName)` | `0` | | DISTINCTCOUNTSMARTHLL | Returns the count of distinct values of a column as `Integer`. When there are too many distinct values, automatically switch to approximate distinct count using *HyperLogLog*. The switch `threshold` (100\_000 by default) and `log2m` (12 by default) for the *HyperLogLog* can be configured via the optional second argument. |

DISTINCTCOUNTSMARTHLL(playerName),

DISTINCTCOUNTSMARTHLL(playerName, 'threshold=100;log2m=8')

| `0` | | [**DISTINCTCOUNTCPCSKETCH**](https://github.com/pinot-contrib/pinot-docs/blob/latest/configuration-reference/functions/distinctcountcpcsketch.md) | See [Cardinality Estimation](https://docs.pinot.apache.org/release-1.1.0/for-users/user-guide-query/query-syntax/how-to-handle-unique-counting) | | `0` | | [**DISTINCTCOUNTRAWCPCSKETCH**](https://github.com/pinot-contrib/pinot-docs/blob/latest/configuration-reference/functions/distinctcountrawcpcsketch.md) | See [Cardinality Estimation](https://docs.pinot.apache.org/release-1.1.0/for-users/user-guide-query/query-syntax/how-to-handle-unique-counting) | | `0` | | [**DISTINCTCOUNTRAWINTEGERSUMTUPLESKETCH**](https://github.com/pinot-contrib/pinot-docs/blob/latest/configuration-reference/functions/distinctcountrawintegersumtuplesketch.md) | See [Cardinality Estimation](https://docs.pinot.apache.org/release-1.1.0/for-users/user-guide-query/query-syntax/how-to-handle-unique-counting) | | `0` | | [**DISTINCTCOUNTTHETASKETCH**](https://docs.pinot.apache.org/release-1.1.0/configuration-reference/functions/distinctcountthetasketch) | See [Cardinality Estimation](https://docs.pinot.apache.org/release-1.1.0/for-users/user-guide-query/query-syntax/how-to-handle-unique-counting) | | `0` | | [**DISTINCTCOUNTRAWTHETASKETCH**](https://docs.pinot.apache.org/release-1.1.0/configuration-reference/functions/distinctcountrawthetasketch) | See [Cardinality Estimation](https://docs.pinot.apache.org/release-1.1.0/for-users/user-guide-query/query-syntax/how-to-handle-unique-counting) | | `0` | | [**DISTINCTCOUNTTUPLESKETCH**](https://github.com/pinot-contrib/pinot-docs/blob/latest/configuration-reference/functions/distinctcounttuplesketch.md) | See [Cardinality Estimation](https://docs.pinot.apache.org/release-1.1.0/for-users/user-guide-query/query-syntax/how-to-handle-unique-counting) | | `0` | | [**DISTINCTCOUNTULL**](https://github.com/pinot-contrib/pinot-docs/blob/latest/configuration-reference/functions/distinctcountull.md) | See [Cardinality Estimation](https://docs.pinot.apache.org/release-1.1.0/for-users/user-guide-query/query-syntax/how-to-handle-unique-counting) | | `0` | | [**DISTINCTCOUNTRAWULL**](https://github.com/pinot-contrib/pinot-docs/blob/latest/configuration-reference/functions/distinctcountrawull.md) | See [Cardinality Estimation](https://docs.pinot.apache.org/release-1.1.0/for-users/user-guide-query/query-syntax/how-to-handle-unique-counting) | | `0` | | [**SEGMENTPARTITIONEDDISTINCTCOUNT**](https://docs.pinot.apache.org/release-1.1.0/configuration-reference/functions/segmentpartitioneddistinctcount) | Returns the count of distinct values of a column as `Long` when the column is pre-partitioned for each segment, where there is no common value within different segments. This function calculates the exact count of distinct values within the segment, then simply sums up the results from different segments to get the final result. | `SEGMENTPARTITIONEDDISTINCTCOUNT(playerName)` | `0` | | [**SEGMENTPARTITIONEDDISTINCTCOUNT**](https://docs.pinot.apache.org/release-1.1.0/configuration-reference/functions/segmentpartitioneddistinctcount) | Returns the count of distinct values of a column as `Long` when the column is pre-partitioned for each segment, where there is no common value within different segments. This function calculates the exact count of distinct values within the segment, then simply sums up the results from different segments to get the final result. | `SEGMENTPARTITIONEDDISTINCTCOUNT(playerName)` | `0` | | [**SUMVALUESINTEGERSUMTUPLESKETCH**](https://github.com/pinot-contrib/pinot-docs/blob/latest/configuration-reference/functions/sumvaluesintegersumtuplesketch.md) | See [Cardinality Estimation](https://docs.pinot.apache.org/release-1.1.0/for-users/user-guide-query/query-syntax/how-to-handle-unique-counting) | | `0` | | [**LASTWITHTIME(dataColumn, timeColumn, 'dataType')**](https://docs.pinot.apache.org/release-1.1.0/configuration-reference/functions/lastwithtime) | Get the last value of **dataColumn** where the **timeColumn** is used to define the time of dataColumn and the **dataType** specifies the type of dataColumn, which can be `BOOLEAN`, `INT`, `LONG`, `FLOAT`, `DOUBLE`, `STRING` |

LASTWITHTIME(playerScore, timestampColumn, 'BOOLEAN')

LASTWITHTIME(playerScore, timestampColumn, 'INT')

LASTWITHTIME(playerScore, timestampColumn, 'LONG')

LASTWITHTIME(playerScore, timestampColumn, 'FLOAT')

LASTWITHTIME(playerScore, timestampColumn, 'DOUBLE')

LASTWITHTIME(playerScore, timestampColumn, 'STRING')

| `INT: Int.MIN_VALUE LONG: Long.MIN_VALUE FLOAT: Float.NaN DOUBLE: Double.NaN STRING: ""` | | [**FIRSTWITHTIME(dataColumn, timeColumn, 'dataType')**](https://docs.pinot.apache.org/release-1.1.0/configuration-reference/functions/firstwithtime) | Get the first value of **dataColumn** where the **timeColumn** is used to define the time of dataColumn and the **dataType** specifies the type of dataColumn, which can be `BOOLEAN`, `INT`, `LONG`, `FLOAT`, `DOUBLE`, `STRING` |

FIRSTWITHTIME(playerScore, timestampColumn, 'BOOLEAN')

FIRSTWITHTIME(playerScore, timestampColumn, 'INT')

FIRSTWITHTIME(playerScore, timestampColumn, 'LONG')

FIRSTWITHTIME(playerScore, timestampColumn, 'FLOAT')

FIRSTWITHTIME(playerScore, timestampColumn, 'DOUBLE')

FIRSTWITHTIME(playerScore, timestampColumn, 'STRING')

| `INT: Int.MIN_VALUE LONG: Long.MIN_VALUE FLOAT: Float.NaN DOUBLE: Double.NaN STRING: ""` | Deprecated functions: | Function | Description | Example | | ----------- | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------- | --------------------- | | **FASTHLL** | FASTHLL stores serialized HyperLogLog in String format, which performs worse than DISTINCTCOUNTHLL, which supports serialized HyperLogLog in BYTES (byte array) format | `FASTHLL(playerName)` | ## Multi-value column functions The following aggregation functions can be used for multi-value columns | Function | | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | |

COUNTMV
Returns the count of a multi-value column as Long

| |

MINMV
Returns the minimum value of a numeric multi-value column as Double

| |

MAXMV
Returns the maximum value of a numeric multi-value column as Double

| |

SUMMV
Returns the sum of the values for a numeric multi-value column as Double

| |

AVGMV
Returns the average of the values for a numeric multi-value column as Double

| |

MINMAXRANGEMV
Returns the max - min value for a numeric multi-value column as Double

| |

PERCENTILEMV(column, N)
Returns the Nth percentile of the values for a numeric multi-value column as Double

| |

PERCENTILEESTMV(column, N)
Returns the Nth percentile using Quantile Digest as Long

| |

PERCENTILETDIGESTMV(column, N)
Returns the Nth percentile using T-digest as Double

| |

PERCENTILETDIGESTMV(column, N, CF)
Returns the Nth percentile (using compression factor CF) using T-digest as Double

| |

DISTINCTCOUNTMV
Returns the count of distinct values for a multi-value column as Integer

| |

DISTINCTCOUNTBITMAPMV
Returns the count of distinct values for a multi-value column as Integer. This function is accurate for INT or dictionary encoded column, but approximate for other cases where hash codes are used in distinct counting and there may be hash collision.

| |

DISTINCTCOUNTHLLMV
Returns an approximate distinct count using HyperLogLog as Long

| |

DISTINCTCOUNTRAWHLLMV
Returns HyperLogLog response serialized as string. The serialized HLL can be converted back into an HLL and then aggregated with other HLLs. A common use case may be to merge HLL responses from different Pinot tables, or to allow aggregation after client-side batching.

| ## FILTER Clause in aggregation Pinot supports FILTER clause in aggregation queries as follows: ``` 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](https://docs.pinot.apache.org/developers/advanced/null-value-support) enabled, this allows to filter out the null values while performing aggregation as follows: ``` SELECT SUM(COL1) FILTER (WHERE COL1 IS NOT NULL) FROM MyTable WHERE COL3 > 50 ``` In the above query, `COL1` is aggregated only for the non-null values. Without NULL value support, we would have to filter using the default null value. Deprecated functions: | Function | Description | Example | | -------------------------- | -------------------------------------------------------------------------------------------------------------------------------------------------------------- | ------------------------ | | **FASTHLLMV (Deprecated)** | stores serialized HyperLogLog in String format, which performs worse than DISTINCTCOUNTHLL, which supports serialized HyperLogLog in BYTES (byte array) format | `FASTHLLMV(playerNames)` |