Aggregate Functions

This page covers functions that can be used in aggregation queries.

All aggregate functions take arguments which are expressions potentially involving an identifier. The functions operate on a number of values and return a single result. All single argument functions support a DISTINCT option, as in COUNT(DISTINCT x), where all the duplicate values are removed from the input set.

SELECT COUNT(is_even) FROM numbers
-- collection with is_even values [true, true, false, false, true]

SELECT COUNT(DISTINCT is_even) FROM numbers

List of functions defined in this section:

Function	Description
`APPROX_DISTINCT(x[, e])`	Returns the approximate number of distinct elements with a non-null value for field `x`.
`ARBITRARY(x)`	Returns an arbitrary non-null element from the input. Returns null if the input is empty or all null.
`ARRAY_AGG(x)`	Returns an array created from all the elements in `x`.
`AVG(x)`	Returns an average of all the elements in `x`. All elements are implicitly promoted to `float`. Return value is of type `float`.
`BITWISE_AND_AGG(x)`	Returns the bitwise AND of all input values in 2's complement representation.
`BITWISE_OR_AGG(x)`	Returns the bitwise OR of all input values in 2's complement representation.
`BOOL_AND(x)`	Returns `true` if every value in the input is `true`, `false` otherwise. Return value and all arguments are `boolean`.
`BOOL_OR(x)`	Returns `true` if one value in the input is `true`, `false` otherwise. Return value and all arguments are `boolean`.
`COUNT(*)`	Returns the number of input rows.
`COUNT_IF(x)`	Returns the number of elements in `x` which are `true`.
`EVERY(x)`	An alias of `BOOL_AND` function.
`GROUPING(col1, col1, ... col_n)`	Returns the grouping mask, which is a bitmask associating one bit with every column (the first column in the list of arguments corresponds to the most significant bit in the result).
`HMAP_AGG(key, value, op)`	Creates a histogram map aggregate. For each input document, the `op` argument specifies the update semantics for the given `key` `value` pair.
`HMAP_CONTAINS_VALUE(hmap, v)`	Returns whether value `v` occurs in the histogram map aggregate `hmap`.
`HMAP_ELEMENT_AT(hmap, k)`	Returns the value associated with key `k` in the histogram map aggregate `hmap`.
`HMAP_VALUE_COUNT(hmap, v)`	Returns the number of times value `v` occurs in the histogram map aggregate `hmap`.
`MAP_AGG(keys, values)`	Creates an object where the keys are from the first input and the values are from the second input.
`MAX(x)`	Returns the maximum value of all elements in `x`.
`MAX_BY(x, y)`	Returns the value of column `x` associated with the maximum value of column `y`.
`MIN(x)`	Returns the minimum value of all elements in `x`.
`MIN_BY(x, y)`	Returns the value of column `x` associated with the minimum value of column `y`.
`STDDEV_SAMP(x)`	Returns the sample standard deviation of all input values.
`SUM(x)`	Returns the sum of all elements in `x`. Returns a value of type `int` if all of the input elements are `int`, `float` otherwise.