---
title: "Aggregate and analytic functions"
slug: "dev-database-reference-sql-functions-aggregate"
updated: 2026-10-30T18:18:46Z
published: 2026-10-30T18:18:46Z
canonical: "docs.vastdata.com/dev-database-reference-sql-functions-aggregate"
---

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

# Aggregate and analytic functions

*Applies to VAST Cluster 5.5.*

Aggregates that summarise the rows of a query, the `FILTER` clause, and the window functions the VAST Query Engine can run.

Each function has its own page: syntax, what it does, NULLs and edge cases, examples with their output, how it runs in VAST, how to make it faster, limitations and errors, and similar functions. Names that the VAST Query Engine doesn't have are in [all functions A-Z](/develop/docs/dev-database-reference-sql-functions-a-z).

## Counting

| Function | What it does |
| --- | --- |
| [approx_count_distinct](/develop/docs/dev-database-reference-sql-functions-aggregate-approx-count-distinct) | Estimate the number of distinct non-NULL values of any type, where count(DISTINCT x) isn't available. |
| [count_if](/develop/docs/dev-database-reference-sql-functions-aggregate-count-if) (also `countif`) | Count the rows where a BOOLEAN condition is true; countif is the same function. |
| [count](/develop/docs/dev-database-reference-sql-functions-aggregate-count) (also `count()`, `count_star()`, `count(*)`, `count(ALL x)`, `count(x)`) | Count rows, or the rows where a value isn't NULL; count(), count_star() and count(*) are the same. |

## Sums and averages

| Function | What it does |
| --- | --- |
| [avg](/develop/docs/dev-database-reference-sql-functions-aggregate-avg) (also `mean`) | Compute the arithmetic mean of the non-NULL values of a number column or expression; mean is the same function. |
| [geomean](/develop/docs/dev-database-reference-sql-functions-aggregate-geomean) (also `geometric_mean`) | Compute the geometric mean of positive values, the n-th root of their product; geometric_mean is the same function. |
| [product](/develop/docs/dev-database-reference-sql-functions-aggregate-product) | Multiply the non-NULL values of a number column or expression together, as a DOUBLE. |
| [sum](/develop/docs/dev-database-reference-sql-functions-aggregate-sum) | Add up numbers, or count the true values of a BOOLEAN, with the result types and overflow behaviour of each input type. |
| [wavg](/develop/docs/dev-database-reference-sql-functions-aggregate-wavg) (also `weighted_avg`) | Compute a weighted mean, where each value counts in proportion to its weight; weighted_avg is the same function. |

## Smallest, largest and any value

| Function | What it does |
| --- | --- |
| [any_value](/develop/docs/dev-database-reference-sql-functions-aggregate-any-value) | Return one non-NULL value of an expression from the rows of a query, typically to show a column that's the same on every row next to other aggregates. |
| [max](/develop/docs/dev-database-reference-sql-functions-aggregate-max) | Return the largest value of a number, BOOLEAN, DATE, TIME, TIMESTAMP or INTERVAL column across the rows of a query. |
| [min](/develop/docs/dev-database-reference-sql-functions-aggregate-min) | Return the smallest value of a number, BOOLEAN, DATE, TIME, TIMESTAMP or INTERVAL column across the rows of a query. |

## True or false

| Function | What it does |
| --- | --- |
| [bool_and](/develop/docs/dev-database-reference-sql-functions-aggregate-bool-and) | Test whether a BOOLEAN expression is true on every row of a query, skipping NULLs. |
| [bool_or](/develop/docs/dev-database-reference-sql-functions-aggregate-bool-or) | Test whether a BOOLEAN expression is true on at least one row of a query, skipping NULLs. |

## Spread

| Function | What it does |
| --- | --- |
| [stddev_pop](/develop/docs/dev-database-reference-sql-functions-aggregate-stddev-pop) | Compute the population standard deviation of a numeric column across the rows of a query. |
| [stddev_samp](/develop/docs/dev-database-reference-sql-functions-aggregate-stddev-samp) (also `stddev`) | Compute the sample standard deviation of a numeric column across the rows of a query; stddev is the same function. |
| [var_samp](/develop/docs/dev-database-reference-sql-functions-aggregate-var-samp) (also `variance`) | Compute the sample variance of a numeric column across the rows of a query; variance is the same function. |
| [var_pop](/develop/docs/dev-database-reference-sql-functions-aggregate-var-pop) | Compute the population variance of a numeric column: the mean squared distance from the mean, dividing by n. |

## Restricting an aggregate

| Function | What it does |
| --- | --- |
| [FILTER (WHERE ...)](/develop/docs/dev-database-reference-sql-functions-aggregate-filter-clause) | Restrict one aggregate to the rows that meet a condition with FILTER (WHERE ...), so one query can aggregate several subsets. |

## Window functions

| Function | What it does |
| --- | --- |
| [row_number](/develop/docs/dev-database-reference-sql-functions-aggregate-row-number) | Number the rows of a result from 1 with row_number() OVER (), and number them in a chosen order with a sorted subquery. |
| [Window functions](/develop/docs/dev-database-reference-sql-functions-aggregate-window-functions) | Which window functions and OVER (...) forms the VAST Query Engine runs: lag, lead, first_value, row_number and running aggregates over the order rows arrive in, and what to use for the rest. |

## See also

- [SQL functions](/develop/docs/dev-database-reference-sql-functions-index) and [all functions A-Z](/develop/docs/dev-database-reference-sql-functions-a-z).