# 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](/docs/data/data-cloud-query-guide/references/dc-sql-reference/filter-clause.md) to apply conditions to individual aggregates. ## General-Purpose Aggregates Common aggregate functions for summarizing data: - [ANY_VALUE](/docs/data/data-cloud-query-guide/references/dc-sql-reference/any-value.md) - Return an arbitrary value from the group. - [APPROX_COUNT_DISTINCT](/docs/data/data-cloud-query-guide/references/dc-sql-reference/approx-count-distinct.md) - Compute an approximate count of distinct values. - [AVG](/docs/data/data-cloud-query-guide/references/dc-sql-reference/avg.md) - Calculate the arithmetic mean. - [BIT_AND](/docs/data/data-cloud-query-guide/references/dc-sql-reference/bit-and.md) - Compute bitwise AND of all values. - [BIT_OR](/docs/data/data-cloud-query-guide/references/dc-sql-reference/bit-or.md) - Compute bitwise OR of all values. - [BOOL_AND](/docs/data/data-cloud-query-guide/references/dc-sql-reference/bool-and.md) - Return `true` if all values are true. - [BOOL_OR](/docs/data/data-cloud-query-guide/references/dc-sql-reference/bool-or.md) - Return `true` if any value is true. - [COUNT](/docs/data/data-cloud-query-guide/references/dc-sql-reference/count.md) - Count rows or non-null values. - [MAX](/docs/data/data-cloud-query-guide/references/dc-sql-reference/max.md) - Find the maximum value. - [MAX_BY](/docs/data/data-cloud-query-guide/references/dc-sql-reference/max-by.md) - Return the value from the row with the maximum key. - [MIN](/docs/data/data-cloud-query-guide/references/dc-sql-reference/min.md) - Find the minimum value. - [MIN_BY](/docs/data/data-cloud-query-guide/references/dc-sql-reference/min-by.md) - Return the value from the row with the minimum key. - [SUM](/docs/data/data-cloud-query-guide/references/dc-sql-reference/sum.md) - Calculate the total. ## Statistical Aggregates Functions for statistical analysis: - [CORR](/docs/data/data-cloud-query-guide/references/dc-sql-reference/corr.md) - Compute correlation coefficient. - [COVAR_POP](/docs/data/data-cloud-query-guide/references/dc-sql-reference/covar-pop.md) - Compute population covariance. - [COVAR_SAMP](/docs/data/data-cloud-query-guide/references/dc-sql-reference/covar-samp.md) - Compute sample covariance. - [KURTOSIS](/docs/data/data-cloud-query-guide/references/dc-sql-reference/kurtosis.md) - Compute unbiased excess kurtosis. - [SKEWNESS_POP](/docs/data/data-cloud-query-guide/references/dc-sql-reference/skewness-pop.md) - Compute population skewness. - [SKEWNESS_SAMP](/docs/data/data-cloud-query-guide/references/dc-sql-reference/skewness-samp.md) - Compute sample skewness. - [STDDEV_POP](/docs/data/data-cloud-query-guide/references/dc-sql-reference/stddev-pop.md) - Compute population standard deviation. - [STDDEV/STDDEV_SAMP](/docs/data/data-cloud-query-guide/references/dc-sql-reference/stddev.md) - Compute sample standard deviation. - [VAR_POP](/docs/data/data-cloud-query-guide/references/dc-sql-reference/var-pop.md) - Compute population variance. - [VARIANCE/VAR_SAMP](/docs/data/data-cloud-query-guide/references/dc-sql-reference/variance.md) - 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](/docs/data/data-cloud-query-guide/references/dc-sql-reference/array-agg.md) - Collect values into an array, with optional ordering and NULL handling. - [JSON_ARRAYAGG](/docs/data/data-cloud-query-guide/references/dc-sql-reference/json-arrayagg.md) - Collect values into a JSON array, with optional ordering and NULL handling. - [LISTAGG](/docs/data/data-cloud-query-guide/references/dc-sql-reference/listagg.md) - Concatenate values into a single delimited string. ## Ordered-Set Aggregates Aggregate functions that use the `WITHIN GROUP` syntax: - [FIRST_VALUE](/docs/data/data-cloud-query-guide/references/dc-sql-reference/first-value-ordered-set.md) - Return the `` from the first row within each `GROUP BY` group after ordering by specified keys. - [MODE](/docs/data/data-cloud-query-guide/references/dc-sql-reference/mode.md) - Return the most frequent value. - [PERCENTILE_CONT](/docs/data/data-cloud-query-guide/references/dc-sql-reference/percentile-cont.md) - Compute continuous percentile with interpolation. - [PERCENTILE_DISC](/docs/data/data-cloud-query-guide/references/dc-sql-reference/percentile-disc.md) - 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](/docs/data/data-cloud-query-guide/references/dc-sql-reference/grouping.md) - Identify which columns are aggregated in grouping sets. - [FILTER Clause](/docs/data/data-cloud-query-guide/references/dc-sql-reference/filter-clause.md) - Filter rows passed to aggregate functions. ## Examples ### Basic Aggregation Calculate summary statistics. ```sql 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. ```sql 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. ```sql 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. ```sql SELECT stddev(score) AS score_stddev, variance(score) AS score_variance FROM test_results; ``` ### Percentile Calculation Find median and quartiles. ```sql 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](/docs/data/data-cloud-query-guide/references/dc-sql-reference/select-group-by.md) - [Window Functions](/docs/data/data-cloud-query-guide/references/dc-sql-reference/window.md)