> ## Documentation Index
> Fetch the complete documentation index at: https://docs.e6data.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Aggregations

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

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.

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
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.

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
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.

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
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.

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
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](https://docs.e6data.com/transformation/sql/functions-aggregate.md) for signatures and statistical aggregates. `collect_list` and `collect_set` are unavailable in Unified.


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.