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

# Aggregation

> GROUP BY, HAVING, and the aggregate function set.

**Applies to:** Embedded ❌, Enterprise ✅

Use SQL aggregates to summarize records, either across a whole table or
within groups. Common examples include counts by category, totals and averages.

## Grouping

```sql SQL icon="database" theme={"theme":{"light":"vitesse-light","dark":"catppuccin-mocha"}}
SELECT category, count(*) AS n, avg(score) AS mean_score
FROM documents
GROUP BY category
HAVING count(*) > 10
ORDER BY n DESC
```

`WHERE` filters rows before grouping; `HAVING` filters groups after. Use `WHERE` for row-level conditions and `HAVING` for conditions on aggregate
results. The planner may push eligible predicates earlier in the query.

Grouping by several expressions produces one row per distinct combination:

```sql SQL icon="database" theme={"theme":{"light":"vitesse-light","dark":"catppuccin-mocha"}}
SELECT category, source, count(*)
FROM documents
GROUP BY category, source
```

## Aggregate without grouping

Omitting `GROUP BY` aggregates the whole table into one row, which is the usual way to
ask how big something is:

```sql SQL icon="database" theme={"theme":{"light":"vitesse-light","dark":"catppuccin-mocha"}}
SELECT count(*), min(created_at), max(created_at) FROM documents
```

`count(*)` counts rows; `count(col)` counts non-null values, which is how you
measure how populated a [computed column](https://docs.lancedb.com/feature-engineering/add-computed-column.md)
is before deciding to refresh it again.

## The function set

Registered in DataFusion 54. Names below are what is actually available.

**General**

```text theme={"theme":{"light":"vitesse-light","dark":"catppuccin-mocha"}}
count  sum  min  max  avg  median
bit_and  bit_or  bit_xor  bool_and  bool_or
first_value  last_value  nth_value  grouping
```

**Statistical**

```text theme={"theme":{"light":"vitesse-light","dark":"catppuccin-mocha"}}
stddev  stddev_pop  stddev_samp
var  var_pop  var_samp
corr  covar_pop  covar_samp
regr_slope  regr_intercept  regr_count  regr_r2
regr_avgx  regr_avgy  regr_sxx  regr_syy  regr_sxy
```

**Approximate**

```text theme={"theme":{"light":"vitesse-light","dark":"catppuccin-mocha"}}
approx_distinct  approx_median
approx_percentile_cont  approx_percentile_cont_with_weight
percentile_cont
```

**Collecting**

```text theme={"theme":{"light":"vitesse-light","dark":"catppuccin-mocha"}}
array_agg  string_agg
```

The approximate functions are the ones worth reaching for on a large table.
`approx_distinct` answers "roughly how many distinct values" for a fraction of
what `count(DISTINCT ...)` costs, which can help with high-cardinality columns. Measure the accuracy and runtime
for your workload.

`DISTINCT` works inside an aggregate: `count(DISTINCT category)`. `FILTER`
narrows one aggregate without narrowing the others:

```sql SQL icon="database" theme={"theme":{"light":"vitesse-light","dark":"catppuccin-mocha"}}
SELECT
    count(*) AS total,
    count(*) FILTER (WHERE score > 0.9) AS confident
FROM documents
```

## Aggregating a sample

For an estimate over a very large table, aggregate a
[sample](https://docs.lancedb.com/sql/dql/sampling.md) rather than the whole thing:

```sql SQL icon="database" theme={"theme":{"light":"vitesse-light","dark":"catppuccin-mocha"}}
SELECT category, count(*) * 100 AS estimated
FROM documents TABLESAMPLE (1)
GROUP BY category
```

For a uniform percentage sample, counts and sums can be scaled by the inverse
of the sample fraction to estimate whole-table values. Do not apply that scaling
to averages, minima or maxima. Small samples can miss rare categories; consider
the sampling method and uncertainty when interpreting the result.

## What aggregation does not do

Aggregates run over rows the query already produced. Whether a statement can
include [vector search](https://docs.lancedb.com/search/vector-search.md)
is described under [SQL vector search](https://docs.lancedb.com/basics/sql.md#no-vector-sql). If your server
does not support that query form, run the
search in an SDK and aggregate its result, or materialize the search output
first.


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