Statistical aggregate functions permit statistical analysis of numeric data. See also “Aggregate functions”.
These functions are true aggregate functions in that they can compute values for a group of rows as determined by the query’s group by clause. As with other basic aggregate functions, such as max or min, computation ignores null values in the input. Also, regardless of the domain of the expression being analyzed, all variance and standard deviation computation uses the Institute of Electrical and Electronic Engineers (IEEE) double-precision, floating-point standard.
If the input to any variance or standard deviation function is the empty set, then each function returns a null value. If the input to any variance or standard deviation function is a single value, then each function returns 0.
Statistical aggregate functions are similar to the avg aggregate function in that:
The syntax is:
statistical_agg_function_name ([all | distinct] expression)
Only expressions with numerical datatypes are valid.
Null values do not participate in the calculation.
The result is NULL only if no data participates in the calculation.
The distinct or all clauses can precede the expression (the default is all).
You can use statistical aggregates as vector aggregates (with group by), scalar aggregates (without group by), or in the compute clause.
Unlike the avg aggregate function, however, the results are:
Always of float datatype (that is, a double-precision floating-point), whereas for the avg aggregate, the datatype of the result is the same as that of the expression (with exceptions).
0.0 for a single data point.
