Aggregate Functions

Applies to: ✅ Data Cloud SQL ✅ Tableau Hyper API

Aggregate functions compute a single result from a set of input values. The three types of aggregate functions are General-Purpose Aggregates, Statistical Aggregates, and Ordered Set Aggregates. You can also use the GROUPING function with grouping sets to check which columns the results were aggregated on.

Built-in general-purpose aggregate functions.

FunctionArgument Type(s)Return TypeDescription
any_value(expression)Any typeSame as argument data typeAn arbitrary, implementation-defined value from the set of input values. The result is non-deterministic.
approx_count_distinct(expression, e)any, double precisionbigintComputes approximation of count(distinct expression), with expected relative error e. Supported values of e are in range (0.002, 1]. The e argument is optional, if omitted, the value 0.023 is used (2.3% expected relative error to real distinct count).
avg(expression)any numerical typenumeric with a scale of 6 for any integer-type argument and numeric arguments with a scale less than 6, double precision for a floating-point argument. Otherwise the same as the argument data typethe average (arithmetic mean) of all input values
bit_and(expression)integral typessame as argument data typethe bitwise AND of all non-null input values, or null if none
bit_or(expression)integral typessame as argument data typethe bitwise OR of all non-null input values, or null if none
bool_and(expression)boolbooltrue if all input values are true; otherwise false
bool_or(expression)boolbooltrue if at least one input value is true; otherwise false
count(*)bigintnumber of input rows
count(expression)anybigintnumber of input rows for which the value of <expression> is not null
every(expression)boolboolequivalent to bool_and
max(expression)anysame as argument typemaximum value of <expression> across all input values
min(expression)anysame as argument typeminimum value of <expression> across all input values
sum(expression)any numerical typebigint for any integer-type argument, NUMERIC(38,s) for any NUMERIC(p,s)sum of <expression> across all input values

Except for count, these functions return a null value if no rows are selected. The coalesce function can be used to substitute zero for null if necessary.

The next table shows aggregate functions typically used in statistical analysis. In all cases, null is returned if the computation is meaningless. For example, if the number of input rows for which all input expressions are non-null is zero.

FunctionArgument TypeReturn TypeDescription
corr(Y, X)double precisiondouble precisioncorrelation coefficient
covar_pop(Y, X)double precisiondouble precisionpopulation covariance
covar_samp(Y, X)double precisiondouble precisionsample covariance
stddev(expression)any numerical typeNUMERIC(38,6) for any integer-type or numeric argument, double precision for a floating-point argumentalias for stddev_samp
stddev_pop(expression)any numerical typeNUMERIC(38,6) for any integer-type or numeric argument, double precision for a floating-point argumentpopulation standard deviation of the input values
stddev_samp(expression)any numerical typeNUMERIC(38,6) for any integer-type or numeric argument, double precision for a floating-point argumentsample standard deviation of the input values
variance(<expression>)any numerical typeNUMERIC(38,6) for any integer-type or numeric argument, double precision for a floating-point argumentalias for var_samp
var_pop(<expression>)any numerical typeNUMERIC(38,6) for any integer-type or numeric argument, double precision for a floating-point argumentpopulation variance of the input values (square of the population standard deviation)
var_samp(<expression>)any numerical typeNUMERIC(38,6) for any integer-type or numeric argument, double precision for a floating-point argumentsample variance of the input values (square of the sample standard deviation)

Casting the input of an aggregate function can be used to force a different output type. For example, you can use VAR_POP(CAST(A AS DOUBLE PRECISION)) to get a double precision result independent of the type of the column A.

Aggregate functions that use the ordered-set aggregate syntax:

FunctionDirect Argument Type(s)Aggregated Argument Type(s)Return TypeDescription
mode() WITHIN GROUP (ORDER BY sort_expression)any sortable typesame as sort expressionreturns the most frequent input value (arbitrarily choosing the first one if there are multiple equally-frequent results)
percentile_cont(fraction) WITHIN GROUP (ORDER BY sort_expression)double precisiondouble precision or intervalsame as sort expressioncontinuous percentile: returns a value corresponding to the specified fraction in the ordering, interpolating between adjacent input items if needed
percentile_disc(fraction) WITHIN GROUP (ORDER BY sort_expression)double precisionany sortable typesame as sort expressiondiscrete percentile: returns the first input value whose position in the ordering equals or exceeds the specified fraction

All ordered-set aggregates ignore null values in their sorted input. For those that take a <fraction> parameter, the fraction value must be between 0 and 1. If not, an error is thrown. However, a null fraction value produces a null result.

Returns a bit mask indicating which GROUP BY expressions aren’t included in the current grouping set. Bits are assigned with the rightmost argument corresponding to the least-significant bit. Each bit is 0 if the corresponding expression is in the grouping rules of the grouping set that created the current result row. If it isn’t, it’s 1. You can use at most 31 expressions as arguments.

The GROUPING function is used with grouping sets to distinguish result rows. The arguments to the GROUPING function aren’t evaluated, but they must exactly match expressions given in the GROUP BY clause of the associated query level. For example:

Here, the grouping value 0 in the first four rows shows that they were grouped normally, over both the grouping columns. The value 1 means that model wasn’t grouped by in the next two rows. The value 3 means that neither make nor model was grouped by in the last row, which is an average of all the input rows.