Aggregate Functions

Aggregate Functions

Aggregate functions take multiple values from documents, perform calculations, and return a single value as the result. The function names are case insensitive.

You can only use aggregate functions in SELECT, LETTING, HAVING, and ORDER BY clauses. When using an aggregate function in a query, the query operates as an aggregate query.

Aggregate functions take one argument, which is used to compute the aggregate function. The COUNT function can also take a wildcard (*) or a path with a wildcard (path.*) as its argument.

If there is no input row for the group, COUNT functions return 0. All other aggregate functions return NULL.

ARRAY_AGG(expression)

Returns array of the non-MISSING values in the group, including NULL values.

ARRAY_AGG(DISTINCT expression)

Returns array of the distinct non-MISSING values in the group, including NULL values.

AVG(expression)

Returns arithmetic mean (average) of all the number values in the group.

AVG(DISTINCT expression)

Returns arithmetic mean (average) of all the distinct number values in the group.

COUNT(*)

Returns count of all the input rows for the group, regardless of value.

COUNT(expression)

Returns count of all the non-NULL and non-MISSING values in the group.

COUNT(DISTINCT expression)

Returns count of all the distinct non-NULL and non-MISSING values in the group.

MAX(expression)

Returns the maximum non-NULL, non-MISSING value in the group in N1QL collation order.

Example: Max of an integer field -- Finding the northernmost latitude of any hotel in travel-sample.
SELECT MAX(geo.lat) AS Latitude FROM `travel-sample` WHERE type="hotel";

Result:
[
  {
    "Latitude": 60.15356
  }
]

MIN(expression)

Returns the minimum non-NULL, non-MISSING value in the group in N1QL collation order.

SUM(expression)

Returns sum of all the number values in the group.

SUM(DISTINCT expression)

Returns arithmetic sum of all the distinct number values in the group.