Aggregate functions
Aggregate functions (or aggregations) are functions that combine multiple values from a group of entries into one, thus collapsing the group into a single entry.
If you add an aggregation to a dimension, it becomes a measure.
Usage Restrictions
There are the following features of using aggregations:
- An aggregate function cannot be nested into another aggregation. The following usage is forbidden:
MAX(SUM([Sales])). Any expression can be aggregated only once. - A function or operator cannot have aggregate and non-aggregate expressions as its arguments simultaneously. The following usage is forbidden:
CONCAT([Profit], SUM([Profit])).
ALL_CONCAT
Syntax:ALL_CONCAT( expression [ , separator ] )
Returns a string that contains all grouped values of expression delimited by separator (if separator is not specified, a comma is used).
ANY
Syntax:ANY( value )
Returns one of the values of value from the group. This is a nondeterministic aggregation — the result may vary for the same data over multiple queries.
ARG_MAX
Syntax:ARG_MAX( value, comp )
Returns value for the maximum value of comp in the group.
ARG_MIN
Syntax:ARG_MIN( value, comp )
Returns value for the minimum value of comp in the group.
AVG
Syntax:AVG( value )
Returns the average of all values. Applicable to numeric data types as well as Date | Datetime.
AVG_IF
Syntax:AVG_IF( expression, condition )
Returns the average of all values that meet the condition condition. If the values don't exist, it returns NULL. Applicable to numeric data types only.
COUNT
Syntax:COUNT( [ value ] )
Returns the number of items in the group.
COUNT_IF
Syntax:COUNT_IF( condition )
Returns the number of items in the group meeting the condition condition.
COUNTD
Syntax:COUNTD( value )
Returns the number of unique values in the group.
See also COUNTD_APPROX.
COUNTD_APPROX
Syntax:COUNTD_APPROX( value )
Returns the approximate number of unique values in the group. Faster than COUNTD, but doesn't guarantee accuracy.
COUNTD_IF
Syntax:COUNTD_IF( expression, condition )
Returns the number of unique values in the group that meet the condition condition.
See also COUNTD_APPROX.
MAX
Syntax:MAX( value )
Returns the maximum value.
If value:
- number — Returns the largest number.
- date — Returns the latest date.
- string — Returns the last value in the alphabetic order.
MEDIAN
Syntax:MEDIAN( value )
Returns the median value.
MIN
Syntax:MIN( value )
Returns the minimum value.
If value:
- number — Returns the smallest number.
- date — Returns the earliest date.
- string — Returns the first value in the alphabetic order.
QUANTILE
Syntax:QUANTILE( value, quant )
Returns the precise value-level quantile (value should be in range from 0 to 1).
QUANTILE_APPROX
Syntax:QUANTILE_APPROX( value, quant )
Returns the approximate value-level quantile (value should be in range from 0 to 1).
STDEV
Syntax:STDEV( value )
Returns the statistical standard deviation of all values in the expression based on a selection from the population.
STDEVP
Syntax:STDEVP( value )
Returns the statistical standard deviation of all values in the expression based on the biased population.
SUM
Syntax:SUM( value )
Returns the sum of all expression values. Applicable to numeric data types only.
SUM_IF
Syntax:SUM_IF( expression, condition )
Returns the sum of all the expression values that meet the condition condition. Applicable to numeric data types only.
TOP_CONCAT
Syntax:TOP_CONCAT( expression, amount [ , separator ] )
Returns a string that contains top amount grouped values of expression delimited by separator (if separator is not specified, a comma is used).
VAR
Syntax:VAR( value )
Returns the statistical variance of all values in an expression based on a selection from the population.
VARP
Syntax:VARP( value )
Returns the statistical variance of all values in an expression across the entire population.