Aggregate Functions

Applies to: ✅ Data 360 SQL ✅ Tableau Hyper API

Functions that compute a single result from a set of input values. Use aggregate functions to:

  • Summarize data by computing counts, sums, and averages.
  • Analyze distributions with statistical functions like standard deviation and variance.
  • Find extremes with MIN and MAX values.
  • Compute percentiles and identify modes in datasets.
  • NULL handling: Except for COUNT, aggregate functions return NULL if no rows are selected. Use COALESCE to substitute a default value.
  • GROUP BY requirement: Non-aggregated fields in the SELECT clause must appear in the GROUP BY clause.
  • FILTER clause: Use the FILTER clause to apply conditions to individual aggregates.

Common aggregate functions for summarizing data:

  • ANY_VALUE - Return an arbitrary value from the group.
  • APPROX_COUNT_DISTINCT - Approximate count of distinct values.
  • AVG - Calculate the arithmetic mean.
  • BIT_AND - Compute bitwise AND of all values.
  • BIT_OR - Compute bitwise OR of all values.
  • BOOL_AND - True if all values are true.
  • BOOL_OR - True if any value is true.
  • COUNT - Count rows or non-null values.
  • EVERY - True if all values are true (alias for BOOL_AND).
  • MAX - Find the maximum value.
  • MIN - Find the minimum value.
  • SUM - Calculate the total.

Functions for statistical analysis:

Cast the input of an aggregate function to force a different output type. For example, use VAR_POP(CAST(A AS DOUBLE PRECISION)) to get a double precision result.

Aggregate functions that use the WITHIN GROUP syntax:

  • MODE - Return the most frequent value.
  • PERCENTILE_CONT - Compute continuous percentile with interpolation.
  • PERCENTILE_DISC - Compute discrete percentile without interpolation.

All ordered-set aggregates ignore NULL values in their sorted input. The fraction parameter must be between 0 and 1.

  • GROUPING - Identify which columns are aggregated in grouping sets.
  • FILTER Clause - Filter rows passed to aggregate functions.

Calculate summary statistics.

Summarize data by category.

Compute multiple filtered aggregates in one query.

Compute standard deviation and variance.

Find median and quartiles.