--- title: "avg" slug: "dev-database-reference-sql-functions-aggregate-avg" updated: 2026-10-30T18:18:46Z published: 2026-10-30T18:18:46Z canonical: "docs.vastdata.com/dev-database-reference-sql-functions-aggregate-avg" --- > ## 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. # avg *Applies to VAST Cluster 5.5.* *[Functions](/develop/docs/dev-database-reference-sql-functions-index) › [Aggregate and analytic functions](/develop/docs/dev-database-reference-sql-functions-aggregate) › Sums and averages.* Returns the arithmetic mean of the non-NULL values: their sum divided by how many there are. `mean` is the same function. ## Syntax ``` avg(x) mean(x) ``` | Argument | Type | Description | | --- | --- | --- | | `x` | a number: any integer type, `FLOAT`, `DOUBLE` or `DECIMAL` | The values to average. NULLs are skipped. | **Returns:** `DOUBLE`, for every input type, `DECIMAL` included. **Aliases:** `mean`. ## Set up the examples The examples connect with the ADBC driver, as in [VAST Query Engine (ADBC)](/develop/docs/dev-database-integrations-adbc), and work in a schema called `docs_sql_fn_aggregate_avg`. Set `QE_ENDPOINT`, `AWS_ACCESS_KEY_ID`, `AWS_SECRET_ACCESS_KEY` and `VASTDB_BUCKET` ([SQL reference](/develop/docs/dev-database-reference-sql-index#example-connect-and-use-a-transaction)), and run the blocks in order in one Python session. Below each block is the output you should see. This block connects, defines the `run()` and `plan()` helpers, and creates a table of four sensor readings: a temperature, a level, a price and an OK flag. The last row has no values: ``` import os import re import adbc_driver_manager import adbc_driver_manager.dbapi as dbapi import adbc_driver_vastdb BUCKET = os.environ["VASTDB_BUCKET"] SCHEMA = f'"{BUCKET}/docs_sql_fn_aggregate_avg"' conn = dbapi.connect( driver=adbc_driver_vastdb.get_driver_path(), db_kwargs={ "vast.db.endpoint": os.environ["QE_ENDPOINT"], "vast.db.access_key": os.environ["AWS_ACCESS_KEY_ID"], "vast.db.secret_key": os.environ["AWS_SECRET_ACCESS_KEY"], }, autocommit=True, ) def server_message(e): """The server's message in a driver error, without the request ID or the echoed statement.""" text = re.sub(r"\[req=0x[0-9a-f]+\]: ", "", str(e)) found = (re.search(r'message: "(.*?)", request_id', text, re.S) or re.search(r"description: (.*)\)", text, re.S)) message = found.group(1) if found else text.strip().splitlines()[-1] message = re.sub(r"^Plan query: '.*?' failed with: ", "", message, flags=re.S) message = re.split(r"\\n|\n", message)[0].replace('\\"', '"') return message.replace(BUCKET, "") def run(sql): """Run one statement. Print the rows it returns, or the error.""" try: with conn.cursor() as cur: cur.execute(sql) rows = cur.fetchall() if cur.description else [] except (adbc_driver_manager.Error, OSError) as e: print(f"{type(e).__name__}: {server_message(e)}") else: print(rows) def plan(sql): """Print the plan without its ~N Rows lines: the table's row count, which can still be 0 just after an insert.""" with conn.cursor() as cur: cur.execute("EXPLAIN " + sql) steps = cur.fetchall()[0][1] print("\n".join(line for line in steps.splitlines() if "Rows" not in line)) TABLE = f"{SCHEMA}.readings" run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}") run(f"CREATE TABLE IF NOT EXISTS {TABLE} " "(id BIGINT, temperature DOUBLE, level INTEGER, price DECIMAL(10,2), ok BOOLEAN)") run(f"INSERT INTO {TABLE} VALUES " "(1, 18.5, 10, 1.25, true), " "(2, 21.0, 20, 2.50, true), " "(3, 19.5, 30, 3.75, false), " "(4, NULL, NULL, NULL, NULL)") ``` ``` [] [] [(4,)] ``` ## What it does `avg(x)` is an aggregate: it adds up the non-NULL values of `x` over the rows of the query and divides by how many there are. It's the same as `sum(x) / count(x)`, not `sum(x) / count(*)`: a row where `x` is NULL counts neither in the sum nor in the number of values. The result is a `DOUBLE` for every input type. An average of `INTEGER` values has a fractional part, and an average of `DECIMAL` values is a `DOUBLE` too, not a `DECIMAL`. Round it for display with [round](/develop/docs/dev-database-reference-sql-functions-numeric-round). ## NULLs and edge cases | Input | Result | | --- | --- | | NULL values | Skipped: not in the sum, not in the count | | Only NULLs, or `avg(NULL)` | NULL | | No rows (an empty table, or a `WHERE` that matches nothing) | NULL | | `avg(42)` with no `FROM` | `42.0` | | `NaN` among the values, or `inf` and `-inf` | `nan` | | `DOUBLE` values whose sum is too large | `inf` | | `DECIMAL(38, s)` values whose sum is beyond 38 digits | Error: `Overflow in HUGEINT addition: ...` | | A `BOOLEAN` | Error: `No function matches the given name and argument types 'avg(BOOLEAN)'`; use `avg(b::INTEGER)` | | A string, date, time, timestamp, interval or list | Error: `No function matches the given name and argument types`, for example `'avg(DATE)'` | | `avg(DISTINCT x)`, `avg(x ORDER BY y)` | Error: `The operator 'UNGROUPED_AGGREGATE' type=8 is currently not supported for this usage.` | The same cases, as the cluster returns them. They use the table and helpers from [Set up the examples](/develop/docs/dev-database-reference-sql-functions-aggregate-avg#set-up-the-examples): ``` run(f"SELECT avg(temperature), sum(temperature) / count(temperature), sum(temperature) / count(*) FROM {TABLE}") run(f"SELECT avg(temperature) FROM {TABLE} WHERE id = 4") run(f"SELECT avg(temperature) FROM {TABLE} WHERE id > 100") run("SELECT avg(NULL), avg(42)") run("SELECT avg(x) FROM (VALUES ('NaN'::DOUBLE), (1.0::DOUBLE)) v(x)") run("SELECT avg(x) FROM (VALUES (1.0e308::DOUBLE), (1.0e308::DOUBLE)) v(x)") run(f"SELECT avg(ok) FROM {TABLE}") run(f"SELECT avg(ok::INTEGER) FROM {TABLE}") run(f"SELECT avg(DISTINCT level) FROM {TABLE}") ``` ``` [(19.666666666666668, 19.666666666666668, 14.75)] [(None,)] [(None,)] [(None, 42.0)] [(nan,)] [(inf,)] InternalError: Binder Error: No function matches the given name and argument types 'avg(BOOLEAN)'. You might need to add explicit type casts. [(0.6666666666666666,)] InternalError: The operator 'UNGROUPED_AGGREGATE' type=8 is currently not supported for this usage. ``` ## Examples The average temperature, level and price. `mean` gives the same result as `avg`, and every result is a `DOUBLE`: ``` run(f"SELECT avg(temperature), mean(temperature), avg(level), avg(price) FROM {TABLE}") ``` ``` [(19.666666666666668, 19.666666666666668, 20.0, 2.5)] ``` The average temperature of the OK readings, rounded to one decimal, and the share of readings that are OK: ``` run(f"SELECT round(avg(temperature) FILTER (WHERE ok), 1), avg(ok::INTEGER) FROM {TABLE}") ``` ``` [(19.8, 0.6666666666666666)] ``` ## How it runs in VAST The Query Engine runs on the cluster's CNodes. An `avg` query has two steps, which [EXPLAIN](/develop/docs/dev-database-reference-sql-explain) shows: 1. **A scan in VAST DataBase** (`VAST_SCAN`). It reads only the columns the average uses, and evaluates the `WHERE` conditions it can [push down](/develop/docs/dev-database-reference-pushdowns), so rows that don't match never leave storage. 2. **One aggregate step** (`UNGROUPED_AGGREGATE`) in the Query Engine, which computes the average of the values the scan sends and returns one row. The plan of an average with a pushed-down `WHERE` condition. The condition doesn't appear in the plan, because the scan evaluates it: ``` plan(f"SELECT avg(temperature) FROM {TABLE} WHERE id > 1") ``` ``` ┌───────────────────────────┐ │ UNGROUPED_AGGREGATE │ │ ──────────────────── │ │ Aggregates: │ │ avg(temperature) │ └─────────────┬─────────────┘ ┌─────────────┴─────────────┐ │ VAST_SCAN │ │ ──────────────────── │ │ Function: VAST_SCAN │ │ │ │ Projections: │ │ temperature │ │ │ └───────────────────────────┘ ``` ## Make it faster - **Filter in `WHERE`, not in `FILTER (WHERE ...)`.** A `WHERE` condition that compares a column with a constant is evaluated in VAST DataBase; a `FILTER` condition is evaluated in the aggregate step, after the scan has sent every row. Its plan shows the condition inside `UNGROUPED_AGGREGATE`: ``` plan(f"SELECT avg(temperature) FILTER (WHERE id > 1) FROM {TABLE}") ``` ``` ┌───────────────────────────┐ │ UNGROUPED_AGGREGATE │ │ ──────────────────── │ │ Aggregates: │ │ avg(temperature) FILTER │ │ (WHERE (id > 1)) Filter: │ │ (id > 1) │ └─────────────┬─────────────┘ ┌─────────────┴─────────────┐ │ VAST_SCAN │ │ ──────────────────── │ │ Function: VAST_SCAN │ │ │ │ Projections: │ │ id │ │ temperature │ │ │ └───────────────────────────┘ ``` - **Compute several aggregates in one query.** `avg`, `sum`, `count` and the others in one `SELECT` list share one scan and one aggregate step. - **Keep functions out of the `WHERE` condition.** A function of a column, such as `abs(temperature) > 20`, isn't pushed down, so the scan sends every row. ## Limitations and errors - **Numbers only.** A `BOOLEAN`, a string, a date, a timestamp, an interval or a list fails with `No function matches the given name and argument types`; the error lists the types `avg` takes. Cast a `BOOLEAN` to `INTEGER` to get the share of true values. - **No average of dates or times.** `avg(day)` on a `DATE` or `TIMESTAMP` fails, and so does an average of an `INTERVAL`. Use [min](/develop/docs/dev-database-reference-sql-functions-aggregate-min) and [max](/develop/docs/dev-database-reference-sql-functions-aggregate-max) for the range. - **No comparison with the average in the same query.** `WHERE x > (SELECT avg(x) FROM t)` fails, with `The operator 'UNGROUPED_AGGREGATE' type=8 is currently not supported for this usage.` (as below) or, for some queries, `The operator 'NESTED_LOOP_JOIN' type=31 is currently not supported.`; `SELECT (SELECT avg(x) FROM t)` fails with `The operator 'UNGROUPED_AGGREGATE' type=8 is currently not supported for this usage.` Run the average as its own query and put the value in the second one. - **No `GROUP BY`, `DISTINCT` or `ORDER BY` inside.** An average per group fails with `The operator 'HASH_GROUP_BY' type=9 is currently not supported.`; `avg(DISTINCT x)` and `avg(x ORDER BY y)` fail with `The operator 'UNGROUPED_AGGREGATE' type=8 is currently not supported for this usage.` `favg`, a compensated average, isn't available either. - **Window forms.** `avg(x) OVER (ROWS UNBOUNDED PRECEDING)` runs, as a running average in the order the scan returns the rows. Other `OVER (...)` forms fail with `The operator 'WINDOW' type=6 is currently not supported.` See [window functions](/develop/docs/dev-database-reference-sql-functions-aggregate-window-functions). ``` run(f"SELECT id FROM {TABLE} WHERE temperature > (SELECT avg(temperature) FROM {TABLE})") run(f"SELECT ok, avg(temperature) FROM {TABLE} GROUP BY ok") run(f"SELECT avg(temperature ORDER BY id) FROM {TABLE}") ``` ``` InternalError: The operator 'UNGROUPED_AGGREGATE' type=8 is currently not supported for this usage. InternalError: The operator 'HASH_GROUP_BY' type=9 is currently not supported. InternalError: The operator 'UNGROUPED_AGGREGATE' type=8 is currently not supported for this usage. ``` ## Similar functions | Function | Returns | | --- | --- | | `avg(x)`, `mean(x)` | the arithmetic mean of the non-NULL values | | `sum(x) / count(x)` ([sum](/develop/docs/dev-database-reference-sql-functions-aggregate-sum), [count](/develop/docs/dev-database-reference-sql-functions-aggregate-count)) | the same value | | `sum(x) / count(*)` | the sum divided by every row, NULLs included: lower than `avg` when there are NULLs | | [wavg](/develop/docs/dev-database-reference-sql-functions-aggregate-wavg) `(x, weight)` | a weighted mean | | [geomean](/develop/docs/dev-database-reference-sql-functions-aggregate-geomean) `(x)` | the geometric mean of positive values | | [list_avg](/develop/docs/dev-database-reference-sql-functions-lists-list-avg) `(list)` | the mean of the elements of one list, per row | ``` run(f"SELECT avg(level), sum(level) / count(level), sum(level) / count(*), wavg(level, 1), geomean(level) FROM {TABLE}") run("SELECT list_avg([10, 20, 30])") ``` ``` [(20.0, 20.0, 15.0, 20.0, 18.171205928321406)] [(20.0,)] ``` Clean up: ``` run(f"DROP TABLE IF EXISTS {TABLE}") run(f"DROP SCHEMA IF EXISTS {SCHEMA}") conn.close() ``` ``` [] [] ``` ## See also - [Aggregate and analytic functions](/develop/docs/dev-database-reference-sql-functions-aggregate): all the aggregates, and the ones that aren't available. - [sum](/develop/docs/dev-database-reference-sql-functions-aggregate-sum), [wavg](/develop/docs/dev-database-reference-sql-functions-aggregate-wavg), [geomean](/develop/docs/dev-database-reference-sql-functions-aggregate-geomean) and [FILTER](/develop/docs/dev-database-reference-sql-functions-aggregate-filter-clause). - [Pushdowns](/develop/docs/dev-database-reference-pushdowns) and [EXPLAIN](/develop/docs/dev-database-reference-sql-explain).