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.
Function | Argument Type(s) | Return Type | Description |
---|---|---|---|
any_value(expression) | Any type | Same as argument data type | An arbitrary, implementation-defined value from the set of input values. The result is non-deterministic. |
approx_count_distinct(expression, e) | any, double precision | bigint | Computes 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 type | numeric 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 type | the average (arithmetic mean) of all input values |
bit_and(expression) | integral types | same as argument data type | the bitwise AND of all non-null input values, or null if none |
bit_or(expression) | integral types | same as argument data type | the bitwise OR of all non-null input values, or null if none |
bool_and(expression) | bool | bool | true if all input values are true; otherwise false |
bool_or(expression) | bool | bool | true if at least one input value is true; otherwise false |
count(*) | bigint | number of input rows | |
count(expression) | any | bigint | number of input rows for which the value of <expression> is not null |
every(expression) | bool | bool | equivalent to bool_and |
max(expression) | any | same as argument type | maximum value of <expression> across all input values |
min(expression) | any | same as argument type | minimum value of <expression> across all input values |
sum(expression) | any numerical type | bigint 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.
Function | Argument Type | Return Type | Description |
---|---|---|---|
corr(Y, X) | double precision | double precision | correlation coefficient |
covar_pop(Y, X) | double precision | double precision | population covariance |
covar_samp(Y, X) | double precision | double precision | sample covariance |
stddev(expression) | any numerical type | NUMERIC(38,6) for any integer-type or numeric argument, double precision for a floating-point argument | alias for stddev_samp |
stddev_pop(expression) | any numerical type | NUMERIC(38,6) for any integer-type or numeric argument, double precision for a floating-point argument | population standard deviation of the input values |
stddev_samp(expression) | any numerical type | NUMERIC(38,6) for any integer-type or numeric argument, double precision for a floating-point argument | sample standard deviation of the input values |
variance (<expression> ) | any numerical type | NUMERIC(38,6) for any integer-type or numeric argument, double precision for a floating-point argument | alias for var_samp |
var_pop (<expression> ) | any numerical type | NUMERIC(38,6) for any integer-type or numeric argument, double precision for a floating-point argument | population variance of the input values (square of the population standard deviation) |
var_samp (<expression> ) | any numerical type | NUMERIC(38,6) for any integer-type or numeric argument, double precision for a floating-point argument | sample 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:
Function | Direct Argument Type(s) | Aggregated Argument Type(s) | Return Type | Description |
---|---|---|---|---|
mode() WITHIN GROUP (ORDER BY sort_expression) | any sortable type | same as sort expression | returns 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 precision | double precision or interval | same as sort expression | continuous 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 precision | any sortable type | same as sort expression | discrete 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.