> ## 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.

# Aggregate functions

> 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.

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

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

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


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