Aggregate Functions

aggregate functions compute a single result from a group of rows you can use the distinct keyword with any aggregation function, such as count(distinct col) aggregate functions function syntax purpose any value any value(col) returns an arbitrary, non null, value from the input column the function gives preference to any non null values in the column and returns null only if all rows in the input column are null the any value function is explicitly undefined if you use it for window aggregation and the function generates an error approx count distinct approx count distinct(col) approximate distinct count by using hyper log log (95% confidence interval that the value is within 4 5%) approx sum approx sum(col) allows the aggregation engine to use a faster, non deterministic, ordering to summate floating point columns this summation might lead to minute differences in the result on the order of the machine epsilon for other column types, the aggregation engine uses the standard sum algorithm avg avg(col) average, or arithmetic mean, over the set correlation correlation(col1,col2) sample correlation corr corr(col1,col2) alias for correlation correlationp correlationp(col1,col2) population correlation count count(col) number of rows in the set where values in col are not null count count( ) total number of rows in the table covariance covariance(col1,col2) sample covariance covar samp covar samp(col1, col2) alias for covariance covariancep covariancep(col1,col2) population covariance covar pop covar pop(col1, col2) alias for covariancep max max(col) maximum value in the given column min min(col) minimum value in the given column stdevp stdevp(col) population standard deviation stddev pop stddev pop(col) alias for stdevp stdev stdev(col) sample standard deviation stddev stddev(col) alias for stdev stddev samp stddev samp(col) alias for stdev sum sum(col) sum over the set variance variance(col) sample variance var samp var samp(col) alias for variance variancep variancep(col) population variance var pop var pop(col) alias for variancep product product(col) product over the set skew skew(col) the sample over the set swep swewp(col) the population over the set kurtosis kurtosis(col) the sample over the set kurtosisp kurtosisp(col) the population over the set related links sorted aggregate functions docid\ wds8rkcmp08swtccthtgc math functions and operators docid\ jbcpq ilyezmmxoz8csfr query ocient docid\ pj83zadmfz4dqseq5on7n