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.

• Null handling: Except for COUNT, 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.

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.

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.

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.

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

Calculate summary statistics.

1SELECT
2    count(*) AS total_orders,
3    sum(amount) AS total_revenue,
4    avg(amount) AS avg_order_value,
5    min(amount) AS min_order,
6    max(amount) AS max_order
7FROM orders;

Summarize data by category.

1SELECT
2    category,
3    count(*) AS product_count,
4    avg(price) AS avg_price
5FROM products
6GROUP BY category;

Compute multiple filtered aggregates in one query.

1SELECT
2    count(*) AS total,
3    count(*) FILTER (WHERE status = 'completed') AS completed,
4    count(*) FILTER (WHERE status = 'pending') AS pending
5FROM orders;

Compute standard deviation and variance.

1SELECT
2    stddev(score) AS score_stddev,
3    variance(score) AS score_variance
4FROM test_results;

Find median and quartiles.

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

• GROUP BY Clause
• Window Functions