SAP ASE supports statistical aggregate functions, which permit statistical analysis of numeric data. Statistical aggregate functions include stddev, stddev_samp, stddev_pop, variance, var_samp, and var_pop.
Simple aggregate functions, such as sum, avg, max, min, count_big, and count are allowed only in the select list and in the having and order by clauses as well as the compute clause of a select statement.
These functions, including stddev and variance, 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, their calculations ignore null values in the input. All variance and standard deviation computation uses IEEE double-precision floating-point standard.
If the input to any variance or standard deviation function is an empty set, each aggregate function returns a null value. If the input to any variance or standard deviation function is a single value, then each function returns 0 as its result.
stddev_pop (also stdevp) – standard deviation of a population. Computes the population standard deviation of the provided value expression evaluated for each row of the group (if distinct was specified, then each row that remains after duplicates have been eliminated), defined as the square root of the population variance.
stddev_samp (also stdev, stddev) – standard deviation of a sample. Computes the population standard deviation of the provided value expression evaluated for each row of the group (if distinct was specified, then each row that remains after duplicates have been eliminated), defined as the square root of the sample variance.
var_pop (also varp) – variance of a population. Computes the population variance of value expression evaluated for each row of the group (if distinct was specified, then each row that remains after duplicates have been eliminated), defined as the sum of squares of the difference of value expression from the mean of value expression, divided by the number of rows in the group.
var_samp (also var, variance) – variance of a sample. Computes the sample variance of value expression evaluated for each row of the group (if distinct was specified, then each row that remains after duplicates have been eliminated), defined as the sum of squares of the difference from the mean of the value expression, divided by one less than the number of rows in the group.