Aggregate functions

ReferenceApplies to: Unified engine deployments

Aggregate functions compute a value for a group. They can also appear as aggregate window functions where supported. Use GROUP BY to define groups and HAVING to filter them.

Function	Returns	Meaning
`count(*)`	BIGINT	Number of input rows
`count(value)`	BIGINT	Number of non-null values
`count(DISTINCT value)`	BIGINT	Number of distinct non-null values
`sum(number)`	Resolved numeric type	Sum of non-null inputs
`avg(number)`, `mean(number)`	Resolved numeric type	Mean of non-null inputs
`min(value)`, `max(value)`	Input-compatible type	Extremal non-null value
`stddev(value)`, `stddev_samp(value)`, `std(value)`	Floating point	Sample standard deviation
`stddev_pop(value)`	Floating point	Population standard deviation
`var_sample(value)`, `var_samp(value)`, `variance(value)`	Floating point	Sample variance
`var_pop(value)`	Floating point	Population variance

Group summaries

SELECT i_category,
       COUNT(*) AS item_count,
       SUM(CAST(i_current_price AS DOUBLE)) AS total_price,
       AVG(CAST(i_current_price AS DOUBLE)) AS average_price,
       MIN(i_current_price) AS low_price,
       MAX(i_current_price) AS high_price
FROM demo.tpcds.item
WHERE i_category IS NOT NULL
GROUP BY i_category
ORDER BY i_category;

Statistical aggregates

Sample and population definitions use different denominators. Sample statistics need enough non-null observations; do not replace an undefined statistic with zero without considering the meaning of the result.

SELECT i_category,
       STDDEV_SAMP(CAST(i_current_price AS DOUBLE)) AS price_stddev,
       VAR_POP(CAST(i_current_price AS DOUBLE)) AS population_variance
FROM demo.tpcds.item
WHERE i_category IS NOT NULL
GROUP BY i_category
ORDER BY i_category;

The Native translation of sample variance (var_sample and its aliases) currently emits an unavailable function. Population variance is used in the runnable example.

Aggregate modifiers

DISTINCT applies before aggregation. Supported aggregate filters restrict the inputs for one aggregate without changing the other aggregates in the row. Ordered aggregate arguments and explicit NULL treatment have backend restrictions. collect_list and collect_set are unavailable because their required Spark null and empty-input behavior is not implemented. For an empty ungrouped input, COUNT returns zero. SUM, AVG, MIN, and MAX generally return NULL. Grouped empty input produces no group rows.

Transformation Engine

Learn Laminar

Workloads

SQL reference

HTTP API

Recipes

Operations

Group summaries

Statistical aggregates

Aggregate modifiers

​Group summaries

​Statistical aggregates

​Aggregate modifiers

Group summaries

Statistical aggregates

Aggregate modifiers