--- schema_version: "1" id: 28bdbc50799ee7fa22ee05090223cbe06ba08fb91384c0388e9af786a2211801 title: Aggregate Functions collection: developer version: current locale: en-us portal_url: https://docs.salesforce.com/developer/current/en-us/data-cloud/data-cloud-query-guide/references/data-cloud-sql-reference/aggregate-func/aggregate.md canonical_url: https://developer.salesforce.com/docs/data/data-cloud-query-guide/references/dc-sql-reference/aggregate.html source_site: developer.salesforce.com content_type: documentation content_hash: e593018ae4045ef1f27fbc0e60b2b885ec645e93c48481499985a0413a4017c2 products: - Data 360 Query Guide - Data 360 Developer Documentation --- # 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](https://developer.salesforce.com/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](https://developer.salesforce.com/docs/data/data-cloud-query-guide/references/dc-sql-reference/any-value.md) - Return an arbitrary value from the group. - [APPROX_COUNT_DISTINCT](https://developer.salesforce.com/docs/data/data-cloud-query-guide/references/dc-sql-reference/approx-count-distinct.md) - Compute an approximate count of distinct values. - [AVG](https://developer.salesforce.com/docs/data/data-cloud-query-guide/references/dc-sql-reference/avg.md) - Calculate the arithmetic mean. - [BIT_AND](https://developer.salesforce.com/docs/data/data-cloud-query-guide/references/dc-sql-reference/bit-and.md) - Compute bitwise AND of all values. - [BIT_OR](https://developer.salesforce.com/docs/data/data-cloud-query-guide/references/dc-sql-reference/bit-or.md) - Compute bitwise OR of all values. - [BOOL_AND](https://developer.salesforce.com/docs/data/data-cloud-query-guide/references/dc-sql-reference/bool-and.md) - Return `true` if all values are true. - [BOOL_OR](https://developer.salesforce.com/docs/data/data-cloud-query-guide/references/dc-sql-reference/bool-or.md) - Return `true` if any value is true. - [COUNT](https://developer.salesforce.com/docs/data/data-cloud-query-guide/references/dc-sql-reference/count.md) - Count rows or non-null values. - [EVERY](https://developer.salesforce.com/docs/data/data-cloud-query-guide/references/dc-sql-reference/every.md) - Return `true` if all values are true (alias for BOOL_AND). - [MAX](https://developer.salesforce.com/docs/data/data-cloud-query-guide/references/dc-sql-reference/max.md) - Find the maximum value. - [MAX_BY](https://developer.salesforce.com/docs/data/data-cloud-query-guide/references/dc-sql-reference/max-by.md) - Return the value from the row with the maximum key. - [MIN](https://developer.salesforce.com/docs/data/data-cloud-query-guide/references/dc-sql-reference/min.md) - Find the minimum value. - [MIN_BY](https://developer.salesforce.com/docs/data/data-cloud-query-guide/references/dc-sql-reference/min-by.md) - Return the value from the row with the minimum key. - [SUM](https://developer.salesforce.com/docs/data/data-cloud-query-guide/references/dc-sql-reference/sum.md) - Calculate the total. ## Statistical Aggregates Functions for statistical analysis: - [CORR](https://developer.salesforce.com/docs/data/data-cloud-query-guide/references/dc-sql-reference/corr.md) - Compute correlation coefficient. - [COVAR_POP](https://developer.salesforce.com/docs/data/data-cloud-query-guide/references/dc-sql-reference/covar-pop.md) - Compute population covariance. - [COVAR_SAMP](https://developer.salesforce.com/docs/data/data-cloud-query-guide/references/dc-sql-reference/covar-samp.md) - Compute sample covariance. - [KURTOSIS](https://developer.salesforce.com/docs/data/data-cloud-query-guide/references/dc-sql-reference/kurtosis.md) - Compute unbiased excess kurtosis. - [SKEWNESS_POP](https://developer.salesforce.com/docs/data/data-cloud-query-guide/references/dc-sql-reference/skewness-pop.md) - Compute population skewness. - [SKEWNESS_SAMP](https://developer.salesforce.com/docs/data/data-cloud-query-guide/references/dc-sql-reference/skewness-samp.md) - Compute sample skewness. - [STDDEV/STDDEV_SAMP](https://developer.salesforce.com/docs/data/data-cloud-query-guide/references/dc-sql-reference/stddev.md) - Compute sample standard deviation. - [STDDEV_POP](https://developer.salesforce.com/docs/data/data-cloud-query-guide/references/dc-sql-reference/stddev-pop.md) - Compute population standard deviation. - [VAR_POP](https://developer.salesforce.com/docs/data/data-cloud-query-guide/references/dc-sql-reference/var-pop.md) - Compute population variance. - [VARIANCE/VAR_SAMP](https://developer.salesforce.com/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](https://developer.salesforce.com/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](https://developer.salesforce.com/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](https://developer.salesforce.com/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](https://developer.salesforce.com/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](https://developer.salesforce.com/docs/data/data-cloud-query-guide/references/dc-sql-reference/mode.md) - Return the most frequent value. - [PERCENTILE_CONT](https://developer.salesforce.com/docs/data/data-cloud-query-guide/references/dc-sql-reference/percentile-cont.md) - Compute continuous percentile with interpolation. - [PERCENTILE_DISC](https://developer.salesforce.com/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](https://developer.salesforce.com/docs/data/data-cloud-query-guide/references/dc-sql-reference/grouping.md) - Identify which columns are aggregated in grouping sets. - [FILTER Clause](https://developer.salesforce.com/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](https://developer.salesforce.com/docs/data/data-cloud-query-guide/references/sql-commands/select-group-by.md) - [Window Functions](https://developer.salesforce.com/docs/data/data-cloud-query-guide/references/window.md)