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 such as standard deviation and variance.
• Find extremes with MIN and MAX values.
• Compute percentiles and identify modes in datasets.
• Collect values into an array, a delimited string, or a JSON array.

Considerations

• Null handling: Except for COUNT, ARRAY_AGG and JSON_ARRAYAGG, aggregate functions return NULL when no rows match. Use coalesce(aggregate_func(...), default_value) 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.

General-Purpose Aggregates

Common aggregate functions for summarizing data:

• ANY_VALUE - Return an arbitrary value from the group.
• APPROX_COUNT_DISTINCT - Compute an 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 - Return true if all values are true.
• BOOL_OR - Return true if any value is true.
• COUNT - Count rows or non-null values.
• EVERY - Return true if all values are true (alias for BOOL_AND).
• MAX - Find the maximum value.
• MAX_BY - Return the value from the row with the maximum key.
• MIN - Find the minimum value.
• MIN_BY - Return the value from the row with the minimum key.
• SUM - Calculate the total.

Statistical Aggregates

Functions for statistical analysis:

• CORR - Compute correlation coefficient.
• COVAR_POP - Compute population covariance.
• COVAR_SAMP - Compute sample covariance.
• KURTOSIS - Compute unbiased excess kurtosis.
• SKEWNESS_POP - Compute population skewness.
• SKEWNESS_SAMP - Compute sample skewness.
• STDDEV/STDDEV_SAMP - Compute sample standard deviation.
• STDDEV_POP - Compute population standard deviation.
• VAR_POP - Compute population variance.
• VARIANCE/VAR_SAMP - Compute sample variance.

tip: 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.

Collection Aggregates

Functions that combine values from a group into a single array, string, or JSON array:

• ARRAY_AGG - Collect values into an array, with optional ordering and NULL handling.
• JSON_ARRAYAGG - Collect values into a JSON array, with optional ordering and NULL handling.
• LISTAGG - Concatenate values into a single delimited string.

Ordered-Set Aggregates

Aggregate functions that use the WITHIN GROUP syntax:

• FIRST_VALUE - Return the <value> from the first row within each GROUP BY group after ordering by specified keys.
• MODE - Return the most frequent value.
• PERCENTILE_CONT - Compute continuous percentile with interpolation.
• PERCENTILE_DISC - Compute discrete percentile without interpolation.

For MODE, PERCENTILE_CONT, and PERCENTILE_DISC, NULL values in the sorted input are ignored where noted on each function page. Percentile functions require a fraction between 0 and 1. FIRST_VALUE does not use a fraction. Ordering follows the ORDER BY keys and NULLS FIRST / NULLS LAST modifiers.

Special Functions and Clauses

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

Examples

Basic Aggregation

Calculate summary statistics.

SELECT
    count(*) AS total_orders,
    sum(amount) AS total_revenue,
    avg(amount) AS avg_order_value,
    min(amount) AS min_order,
    max(amount) AS max_order
FROM orders;

Aggregation with GROUP BY

Summarize data by category.

SELECT
    category,
    count(*) AS product_count,
    avg(price) AS avg_price
FROM products
GROUP BY category;

Using FILTER Clause

Compute multiple filtered aggregates in one query.

SELECT
    count(*) AS total,
    count(*) FILTER (WHERE status = 'completed') AS completed,
    count(*) FILTER (WHERE status = 'pending') AS pending
FROM orders;

Statistical Analysis

Compute standard deviation and variance.

SELECT
    stddev(score) AS score_stddev,
    variance(score) AS score_variance
FROM test_results;

Percentile Calculation

Find median and quartiles.

SELECT
    percentile_cont(0.25) WITHIN GROUP (ORDER BY salary) AS q1,
    percentile_cont(0.5) WITHIN GROUP (ORDER BY salary) AS median,
    percentile_cont(0.75) WITHIN GROUP (ORDER BY salary) AS q3
FROM employees;

Related Documentation

• GROUP BY Clause
• Window Functions