Aggregations

ReferenceApplies to: Unified engine deployments

Aggregate functions reduce rows to a value per group. Every selected expression must be grouped or aggregated, apart from supported constant expressions.

GROUP BY

GROUP BY creates one row for each distinct grouping key. NULL grouping keys form a group. WHERE filters source rows before grouping.

SELECT i_category, COUNT(*) AS item_count,
       AVG(CAST(i_current_price AS DOUBLE)) AS average_price,
       MIN(i_current_price) AS minimum_price,
       MAX(i_current_price) AS maximum_price
FROM demo.tpcds.item
GROUP BY i_category
ORDER BY item_count DESC, i_category NULLS LAST;

HAVING

HAVING filters groups after aggregation. Use WHERE for input predicates that should not affect all groups equally.

SELECT i_category, COUNT(*) AS item_count
FROM demo.tpcds.item
WHERE i_category IS NOT NULL
GROUP BY i_category
HAVING COUNT(*) >= 100
ORDER BY item_count DESC, i_category;

ROLLUP, CUBE, and grouping sets

ROLLUP produces hierarchical subtotals. CUBE produces all combinations of grouping keys. GROUPING SETS enumerates the combinations explicitly. Subtotal rows use NULL in omitted dimensions; use GROUPING or GROUPING_ID where supported to distinguish omitted keys from actual null values.

SELECT i_category, COUNT(*) AS item_count
FROM demo.tpcds.item
GROUP BY ROLLUP (i_category)
ORDER BY i_category NULLS LAST;

Counts and empty inputs

COUNT(*) counts rows, including rows with null fields. COUNT(expression) counts non-null values. COUNT(DISTINCT expression) counts distinct non-null values. SUM, AVG, MIN, and MAX ignore null input values. An aggregate query without GROUP BY still produces a row on empty input; COUNT is zero and most value aggregates are NULL. A grouped query with no input has no groups.

SELECT COUNT(*) AS rows_seen,
       COUNT(i_current_price) AS non_null_prices,
       COUNT(DISTINCT i_category) AS distinct_categories
FROM demo.tpcds.item;

See aggregate functions for signatures and statistical aggregates. collect_list and collect_set are unavailable in Unified.

Transformation Engine

Learn Laminar

Workloads

SQL reference

HTTP API

Recipes

Operations

GROUP BY

HAVING

ROLLUP, CUBE, and grouping sets

Counts and empty inputs

​GROUP BY

​HAVING

​ROLLUP, CUBE, and grouping sets

​Counts and empty inputs

GROUP BY

HAVING

ROLLUP, CUBE, and grouping sets

Counts and empty inputs