SQL Reference

Aggregate Functions

Overview

Aggregate functions compute a single result from a group of rows.

The DISTINCT keyword can be used with any aggregation function. For example, COUNT(DISTINCT col).

Aggregate Functions

Function

Syntax

Purpose

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%)

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