Aggregation Functions

Aggregate functions return a single result for a group of rows.

PreviousQuery Syntax NextCardinality Estimation

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

Deprecated functions:

Multi-value column functions

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

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 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: