# 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](https://docs.pinot.apache.org/functions/aggregation/count) | Count of rows | | [SUM](https://docs.pinot.apache.org/functions/aggregation/sum) / [SUMMV](https://docs.pinot.apache.org/functions/aggregation/summv) | Sum of values | | [MIN](https://docs.pinot.apache.org/functions/aggregation/min) / [MINMV](https://docs.pinot.apache.org/functions/aggregation/minmv) | Minimum value | | [MAX](https://docs.pinot.apache.org/functions/aggregation/max) / [MAXMV](https://docs.pinot.apache.org/functions/aggregation/maxmv) | Maximum value | | [AVG](https://docs.pinot.apache.org/functions/aggregation/avg) / [AVGMV](https://docs.pinot.apache.org/functions/aggregation/avgmv) | Average of values | | [MODE](https://docs.pinot.apache.org/functions/aggregation/mode) | Most frequent value | | [HISTOGRAM](https://docs.pinot.apache.org/functions/aggregation/histogram) | Histogram of values | | [SUMPRECISION](https://docs.pinot.apache.org/functions/aggregation/sumprecision) | High-precision sum using BigDecimal | | [ANYVALUE](https://docs.pinot.apache.org/functions/aggregation/anyvalue) | Any arbitrary non-null value from a group | | [BOOLAND](https://docs.pinot.apache.org/functions/aggregation/booland) / [BOOLOR](https://docs.pinot.apache.org/functions/aggregation/boolor) | Logical AND/OR across boolean values | ## Array and String Aggregations | Function | Description | | ------------------------------------------------------------------------------------ | ------------------------------------------ | | [ARRAYAGG](https://docs.pinot.apache.org/functions/aggregation/arrayagg) | Collect values into an array | | [LISTAGG](https://docs.pinot.apache.org/functions/aggregation/listagg) | Concatenate values into a delimited string | | [SUMARRAYLONG](https://docs.pinot.apache.org/functions/aggregation/sumarraylong) | Element-wise sum of long arrays | | [SUMARRAYDOUBLE](https://docs.pinot.apache.org/functions/aggregation/sumarraydouble) | Element-wise sum of double arrays | ## Statistical Functions | Function | Description | | --------------------------------------------------------------------------------------------------------------------------------------------------------------- | ------------------------------------------- | | [SKEWNESS](https://docs.pinot.apache.org/functions/aggregation/skewness) | Skewness of a distribution | | [KURTOSIS](https://docs.pinot.apache.org/functions/aggregation/kurtosis) | Kurtosis of a distribution | | [DISTINCTSUM](https://docs.pinot.apache.org/functions/aggregation/distinctsum) / [DISTINCTAVG](https://docs.pinot.apache.org/functions/aggregation/distinctavg) | Sum/average of distinct values | | [EXPRMIN](https://docs.pinot.apache.org/functions/aggregation/exprmin) / [EXPRMAX](https://docs.pinot.apache.org/functions/aggregation/exprmax) | Project columns at row with min/max measure | | [ARG\_MIN / ARG\_MAX](https://docs.pinot.apache.org/functions/aggregation/arg_min-arg_max) | Project column at row with min/max measure | ## Set and Sketch Functions | Function | Description | | ------------------------------------------------------------------ | -------------------------------------- | | [IDSET](https://docs.pinot.apache.org/functions/aggregation/idset) | Build an IdSet for efficient filtering | For sketch-based functions (FrequentItems, CPC, HLL+, ULL, Tuple), see [Sketch Functions](https://docs.pinot.apache.org/functions/sketch). For funnel analysis functions, see [Funnel Functions](https://docs.pinot.apache.org/functions/funnel). ## Distinct Counting | Function | Description | | -------------------------------------------------------------------------------------------------------- | ------------------------------------ | | [DISTINCT](https://docs.pinot.apache.org/functions/aggregation/distinct) | Exact distinct values | | [DISTINCTCOUNT](https://docs.pinot.apache.org/functions/aggregation/distinctcount) | Exact distinct count | | [DISTINCTCOUNTBITMAP](https://docs.pinot.apache.org/functions/aggregation/distinctcountbitmap) | Distinct count using bitmap | | [DISTINCTCOUNTHLL](https://docs.pinot.apache.org/functions/aggregation/distinctcounthll) | Approximate distinct count using HLL | | [DISTINCTCOUNTTHETASKETCH](https://docs.pinot.apache.org/functions/aggregation/distinctcountthetasketch) | Distinct count using Theta sketch | | [DISTINCTCOUNTSMARTHLL](https://docs.pinot.apache.org/functions/aggregation/distinctcountsmarthll) | Hybrid exact/HLL distinct count | For sketch-based distinct count functions (CPC, HLL+, ULL, Tuple), see [Sketch Functions](https://docs.pinot.apache.org/functions/sketch). ## Percentile Functions | Function | Description | | ------------------------------------------------------------------------------------------ | --------------------------- | | [PERCENTILE](https://docs.pinot.apache.org/functions/aggregation/percentile) | Exact percentile | | [PERCENTILEEST](https://docs.pinot.apache.org/functions/aggregation/percentileest) | Estimated percentile | | [PERCENTILEKLL](https://docs.pinot.apache.org/functions/aggregation/percentilekll) | Percentile using KLL sketch | | [PERCENTILETDIGEST](https://docs.pinot.apache.org/functions/aggregation/percentiletdigest) | Percentile using T-Digest | ## Window-Related Functions | Function | Description | | --------------------------------------------------------------------------------------------------------------------------------------------------------------------- | -------------------------------- | | [FIRST\_VALUE](https://docs.pinot.apache.org/functions/aggregation/first_value) / [LAST\_VALUE](https://docs.pinot.apache.org/functions/aggregation/last_value) | First/last value in window | | [LEAD](https://docs.pinot.apache.org/functions/aggregation/lead) / [LAG](https://docs.pinot.apache.org/functions/aggregation/lag) | Access subsequent/preceding rows | | [FIRSTWITHTIME](https://docs.pinot.apache.org/functions/aggregation/firstwithtime) / [LASTWITHTIME](https://docs.pinot.apache.org/functions/aggregation/lastwithtime) | First/last value by time | ## Multi-Value Column Functions The following aggregation functions can be used for multi-value columns: | Function | Description | | -------------------------------------------------------------------------------------------------- | -------------------------------------------------------------------------- | | [COUNTMV](https://docs.pinot.apache.org/functions/aggregation/countmv) | Returns the count of a multi-value column | | [MINMV](https://docs.pinot.apache.org/functions/aggregation/minmv) | Returns the minimum value of a numeric multi-value column | | [MAXMV](https://docs.pinot.apache.org/functions/aggregation/maxmv) | Returns the maximum value of a numeric multi-value column | | [SUMMV](https://docs.pinot.apache.org/functions/aggregation/summv) | Returns the sum of the values for a numeric multi-value column | | [AVGMV](https://docs.pinot.apache.org/functions/aggregation/avgmv) | Returns the average of the values for a numeric multi-value column | | [MINMAXRANGEMV](https://docs.pinot.apache.org/functions/aggregation/minmaxrangemv) | Returns the `max - min` value for a numeric multi-value column | | [PERCENTILEMV](https://docs.pinot.apache.org/functions/aggregation/percentilemv) | Returns the Nth percentile of a numeric multi-value column | | [PERCENTILEESTMV](https://docs.pinot.apache.org/functions/aggregation/percentileestmv) | Returns the Nth percentile using Quantile Digest for a multi-value column | | [PERCENTILETDIGESTMV](https://docs.pinot.apache.org/functions/aggregation/percentiletdigestmv) | Returns the Nth percentile using T-Digest for a multi-value column | | [DISTINCTCOUNTMV](https://docs.pinot.apache.org/functions/aggregation/distinctcountmv) | Returns the count of distinct values for a multi-value column | | [DISTINCTCOUNTBITMAPMV](https://docs.pinot.apache.org/functions/aggregation/distinctcountbitmapmv) | Returns the count of distinct values using bitmap for a multi-value column | | [DISTINCTCOUNTHLLMV](https://docs.pinot.apache.org/functions/aggregation/distinctcounthllmv) | Returns an approximate distinct count using HLL for a multi-value column | | [DISTINCTCOUNTRAWHLLMV](https://docs.pinot.apache.org/functions/aggregation/distinctcountrawhllmv) | Returns HyperLogLog response serialized as string for a multi-value column | | [DISTINCTSUMMV](https://docs.pinot.apache.org/functions/aggregation/distinctsummv) | Returns the sum of distinct values of a numeric multi-value column | | [DISTINCTAVGMV](https://docs.pinot.apache.org/functions/aggregation/distinctavgmv) | Returns the average of distinct values of a numeric multi-value column | For sketch-based multi-value functions (HLL+), see [Sketch Functions](https://docs.pinot.apache.org/functions/sketch). ## 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](https://docs.pinot.apache.org/build-with-pinot/querying-and-sql/sql-syntax/null-value-support) 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](https://docs.pinot.apache.org/functions/aggregation/distinct_count_off_heap) | [DISTINCTCOUNTHLLPLUS](https://docs.pinot.apache.org/functions/aggregation/distinctcounthll-1) | | [DISTINCTCOUNTRAWHLL](https://docs.pinot.apache.org/functions/aggregation/distinctcountrawhll) | [DISTINCTCOUNTRAWTHETASKETCH](https://docs.pinot.apache.org/functions/aggregation/distinctcountrawthetasketch) | | [DISTINCTCOUNTULL](https://docs.pinot.apache.org/functions/aggregation/distinctcountull) | [FUNNELEVENTSFUNCTIONEVAL](https://docs.pinot.apache.org/functions/aggregation/funneleventsfunctioneval) | | [FUNNELSTEPDURATIONSTATS](https://docs.pinot.apache.org/functions/aggregation/funnelstepdurationstats) | [maxString](https://docs.pinot.apache.org/functions/aggregation/maxstring) | | [minmaxrange](https://docs.pinot.apache.org/functions/aggregation/minmaxrange) | [minString](https://docs.pinot.apache.org/functions/aggregation/minstring) | | [percentilekllmv](https://docs.pinot.apache.org/functions/aggregation/percentilekllmv) | [percentilerawkll](https://docs.pinot.apache.org/functions/aggregation/percentilerawkll) | | [percentilerawkllmv](https://docs.pinot.apache.org/functions/aggregation/percentilerawkllmv) | [SEGMENTPARTITIONEDDISTINCTCOUNT](https://docs.pinot.apache.org/functions/aggregation/segmentpartitioneddistinctcount) | | [VALUEIN](https://docs.pinot.apache.org/functions/aggregation/valuein) | |