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