Aggregate Functions

Aggregate functions perform computations across all values of a grouped field.

If you don’t precede an aggregate function by a group by statement, it treats each line as its own group. Using an aggregate function on an empty set returns null.

avg() or average()

Returns the average of the values of a measure field.
count()

Returns the number of rows that match the query criteria.
first()

Returns the first value for the specified field.
last()

Returns the last value in the tuple for the specified field.
max()

Returns the maximum value of a dimension or measure field.
median()

Returns the median value of a measure field.
min()

Returns the minimum value of a dimension or measure field.
sum()

Returns the sum of a numeric field.
unique()

Returns the count of unique values.
stddev()

Returns the standard deviation of the values in a field. Accepts measure fields (but not expressions) as input.
stddevp()

Returns the population standard deviation of the values in a field. Accepts measure fields as input but not expressions.
var()

Returns the variance of the values in a field. Accepts measure fields as input but not expressions.
varp()

Returns the variance of the values in a field. Accepts measure fields as input but not expressions.
percentile_cont()

Calculates a percentile based on a continuous distribution of the column value.
percentile_disc()

Returns the value corresponding to the specified percentile.
regr_intercept()

Uses two numerical fields to calculate a trend line, then returns the y-intercept value. Use this function to find out the likely value of field_y when field_x is zero.
regr_slope()

Uses two numerical fields to calculate a trend line, then returns the slope. Use this function to learn more about the relationship between two numerical fields.
regr_r2()

Uses two numerical fields to calculate R-squared, or goodness of fit. Use regr_r2() to understand how well the trend line fits your data.
grouping()

Returns 1 if null dimension values are due to higher-level aggregates (which usually means the row is a subtotal or grand total), otherwise returns 0.

See Also

group-by