--- title: "var_pop" slug: "dev-database-reference-sql-functions-aggregate-var-pop" updated: 2026-10-30T18:18:46Z published: 2026-10-30T18:18:46Z canonical: "docs.vastdata.com/dev-database-reference-sql-functions-aggregate-var-pop" --- > ## 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. # var_pop *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) › Spread.* Returns the population variance of the values in a numeric column: the average of the squared differences from their mean, dividing by the number of values (n). NULLs are skipped. ## Syntax ``` var_pop(x) var_pop(x) FILTER (WHERE condition) ``` | Argument | Type | Description | | --- | --- | --- | | `x` | Any number: integer, unsigned integer, `FLOAT`, `DOUBLE` or `DECIMAL` | The values to measure. | **Returns:** `DOUBLE`, whatever the numeric input type. ## 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_var_pop`. 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 two tables: six sensor readings (the last has no values) and a table of extreme `DOUBLE` 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_var_pop"' 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" EXTREMES = f"{SCHEMA}.extremes" run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}") run(f"CREATE TABLE IF NOT EXISTS {TABLE} " "(id BIGINT, sensor VARCHAR, temperature DOUBLE, qty INTEGER, price DECIMAL(10,2), ok BOOLEAN)") run(f"INSERT INTO {TABLE} VALUES " "(1, 's1', 18.5, 3, 2.50, true), (2, 's1', 21.0, 5, 4.00, true), " "(3, 's2', 19.5, 2, 2.50, false), (4, 's2', 24.0, 8, 6.25, true), " "(5, 's3', 22.5, 4, 3.75, true), (6, 's3', NULL, NULL, NULL, NULL)") run(f"CREATE TABLE IF NOT EXISTS {EXTREMES} (id BIGINT, x DOUBLE)") run(f"INSERT INTO {EXTREMES} VALUES " "(1, 1.0e308), (2, 1.0e308), (3, 5e-324), (4, -1.0e308), " "(5, 'inf'::DOUBLE), (6, 'NaN'::DOUBLE), (7, 1.0)") ``` ``` [] [] [(6,)] [] [(7,)] ``` ## What it does `var_pop(x)` takes the non-NULL values of `x` (n of them), computes their mean, and returns the sum of the squared differences from the mean divided by n. It's the variance of the values taken as a whole population. To estimate the variance of a larger population from a sample, divide by n − 1 instead: that's [var_samp](/develop/docs/dev-database-reference-sql-functions-aggregate-var-samp). Integers, unsigned integers, `FLOAT` and `DECIMAL` values are converted to `DOUBLE`, and the result is always a `DOUBLE`. The square root of `var_pop(x)` is the population standard deviation, [stddev_pop](/develop/docs/dev-database-reference-sql-functions-aggregate-stddev-pop). Like every aggregate in the VAST Query Engine, `var_pop` returns one row for the whole query: there's no `GROUP BY`. ## NULLs and edge cases | Input | Result | | --- | --- | | NULLs among the values | Skipped: n counts only the non-NULL values | | Only NULLs, or no rows | `NULL` | | One value | `0.0` (`var_samp` returns `NULL`) | | Every value the same | `0.0` | | `var_pop(NULL)` | `NULL` | | Large values with a small spread (`1e15 + x` for x from 0 to 999) | `83333.25`, the same as for `x` alone | | `BIGINT` values from the smallest to the largest | A `DOUBLE` result, no error | | `DOUBLE` values whose variance is beyond the `DOUBLE` range, such as `1.0e308` and `-1.0e308` | Error: `VARPOP is out of range!` | | `inf`, `-inf` or `NaN` among other values | Error: `VARPOP is out of range!` | | A `VARCHAR`, `BOOLEAN`, `DATE`, `TIMESTAMP`, `TIME`, `BLOB`, `INTERVAL` or list | Error: `No function matches the given name and argument types`, for example `'var_pop(VARCHAR)'` | The same cases, as the cluster returns them. They use the tables and helpers from [Set up the examples](/develop/docs/dev-database-reference-sql-functions-aggregate-var-pop#set-up-the-examples): ``` run(f"SELECT var_pop(temperature), count(*), count(temperature) FROM {TABLE}") run(f"SELECT var_pop(temperature) FROM {TABLE} WHERE id = 6") run(f"SELECT var_pop(temperature) FROM {TABLE} WHERE id > 100") run(f"SELECT var_pop(temperature), var_samp(temperature) FROM {TABLE} WHERE id = 1") run(f"SELECT var_pop(5) FROM {TABLE}") run("SELECT var_pop(NULL)") run("SELECT var_pop(x), var_pop(1e15 + x) FROM range(1000) t(x)") run(f"SELECT var_pop(x) FILTER (WHERE id IN (1, 2)) FROM {EXTREMES}") run(f"SELECT var_pop(x) FILTER (WHERE id IN (1, 2, 3, 4)) FROM {EXTREMES}") run(f"SELECT var_pop(x) FILTER (WHERE id IN (5, 7)) FROM {EXTREMES}") run(f"SELECT var_pop(x) FILTER (WHERE id IN (6, 7)) FROM {EXTREMES}") run(f"SELECT var_pop(sensor) FROM {TABLE}") run(f"SELECT var_pop(ok) FROM {TABLE}") ``` ``` [(3.939999999999999, 6, 5)] [(None,)] [(None,)] [(0.0, None)] [(0.0,)] [(None,)] [(83333.25, 83333.25)] [(0.0,)] OSError: VARPOP is out of range! OSError: VARPOP is out of range! OSError: VARPOP is out of range! InternalError: Binder Error: No function matches the given name and argument types 'var_pop(VARCHAR)'. You might need to add explicit type casts. InternalError: Binder Error: No function matches the given name and argument types 'var_pop(BOOLEAN)'. You might need to add explicit type casts. ``` ## Examples The population variance of each numeric column. The `INTEGER` and `DECIMAL` columns give a `DOUBLE` too: ``` run(f"SELECT var_pop(temperature), var_pop(qty), var_pop(price) FROM {TABLE}") ``` ``` [(3.939999999999999, 4.24, 1.8850000000000002)] ``` The variance of one sensor's readings, with the condition in `WHERE`: ``` run(f"SELECT var_pop(temperature) FROM {TABLE} WHERE sensor = 's2'") ``` ``` [(5.0625,)] ``` The variance of several subsets in one query, with [FILTER](/develop/docs/dev-database-reference-sql-functions-aggregate-filter-clause): ``` run(f"SELECT var_pop(temperature) FILTER (WHERE ok), " f"var_pop(temperature) FILTER (WHERE NOT ok), var_pop(temperature) FROM {TABLE}") ``` ``` [(4.124999999999998, 0.0, 3.939999999999999)] ``` A `BOOLEAN` column needs a cast. The variance of `ok::INTEGER` measures how mixed the true and false values are: ``` run(f"SELECT var_pop(ok::INTEGER) FROM {TABLE}") ``` ``` [(0.16,)] ``` ## How it runs in VAST The Query Engine runs on the cluster's CNodes. An aggregate query has two steps, which [EXPLAIN](/develop/docs/dev-database-reference-sql-explain) shows: 1. **A scan in VAST DataBase** (`VAST_SCAN`) reads only the columns the query uses, and evaluates the `WHERE` conditions it can push down, so rows that don't match never leave storage. A pushed-down condition doesn't appear in the plan. 2. **An aggregate step** (`UNGROUPED_AGGREGATE`) in the Query Engine computes `var_pop` over the rows the scan returns, and returns one row. With the condition in `WHERE`, the scan reads only `temperature` and evaluates `sensor = 's2'` itself: ``` plan(f"SELECT var_pop(temperature) FROM {TABLE} WHERE sensor = 's2'") ``` ``` ┌───────────────────────────┐ │ UNGROUPED_AGGREGATE │ │ ──────────────────── │ │ Aggregates: │ │ var_pop(temperature) │ └─────────────┬─────────────┘ ┌─────────────┴─────────────┐ │ VAST_SCAN │ │ ──────────────────── │ │ Function: VAST_SCAN │ │ │ │ Projections: │ │ temperature │ │ │ └───────────────────────────┘ ``` With the condition in `FILTER`, the scan sends every row, with the `ok` column as well, and the aggregate step applies the condition: ``` plan(f"SELECT var_pop(temperature) FILTER (WHERE ok) FROM {TABLE}") ``` ``` ┌───────────────────────────┐ │ UNGROUPED_AGGREGATE │ │ ──────────────────── │ │ Aggregates: │ │ var_pop(temperature) │ │ FILTER (WHERE ok) Filter:│ │ ok │ └─────────────┬─────────────┘ ┌─────────────┴─────────────┐ │ VAST_SCAN │ │ ──────────────────── │ │ Function: VAST_SCAN │ │ │ │ Projections: │ │ ok │ │ temperature │ │ │ └───────────────────────────┘ ``` ## Make it faster - **Put a condition shared by every aggregate in `WHERE`.** A comparison of a column with a constant is pushed down to VAST DataBase, so fewer rows reach the Query Engine. `FILTER` is evaluated in the aggregate step, after the scan has sent every row; use it when different aggregates need different conditions. - **Compute the spread statistics together.** `var_pop`, `var_samp`, `stddev_pop`, `avg` and `count` in one `SELECT` share one scan. - **Keep the condition on the raw column.** A function of a column in `WHERE`, such as `abs(qty) > 5`, isn't pushed down: it adds a `FILTER` step in the Query Engine. See [Where filters run](/develop/docs/dev-database-reference-sql-select#where-filters-run). ## Limitations and errors - **Numbers only.** A `VARCHAR`, `BOOLEAN`, date or time, `BLOB`, `INTERVAL` or list fails with `No function matches the given name and argument types 'var_pop(VARCHAR)'` (with the type you passed). The error lists the one form: `var_pop(DOUBLE) -> DOUBLE`. Cast a `BOOLEAN` to `INTEGER`. - **One argument.** `var_pop(x, 1)` fails with the same error, for `'var_pop(DOUBLE, INTEGER)'`. - **No `DISTINCT` or `ORDER BY` inside.** `var_pop(DISTINCT x)` and `var_pop(x ORDER BY id)` fail with `The operator 'UNGROUPED_AGGREGATE' type=8 is currently not supported for this usage.` - **No `GROUP BY`.** A grouped query fails with `The operator 'HASH_GROUP_BY' type=9 is currently not supported.` Run one query per group with `WHERE`, use `FILTER` for a few groups, or use [Trino](/develop/docs/dev-database-integrations-trino) or [Spark](/develop/docs/dev-database-integrations-spark). - **Not in `WHERE`.** An aggregate in `WHERE` fails with `WHERE clause cannot contain aggregates!`. To test the result, use `HAVING`. - **As a window function, only a running variance.** `var_pop(x) OVER (ROWS UNBOUNDED PRECEDING)` runs, in the order rows reach the Query Engine; `OVER ()` and windows with `ORDER BY` or `PARTITION BY` fail with `The operator 'WINDOW' type=6 is currently not supported.` See [window functions](/develop/docs/dev-database-reference-sql-functions-aggregate-window-functions). - **Infinite results.** If the variance is beyond the `DOUBLE` range, or the values include `inf`, `-inf` or `NaN`, the query fails with `VARPOP is out of range!`. Exclude those rows in `WHERE`. - **Other SQL dialects' names aren't available.** `var` and `varp` fail with `Scalar Function with name ... does not exist!`. Use `var_pop`. ``` run(f"SELECT var_pop(temperature, 1) FROM {TABLE}") run(f"SELECT var_pop(DISTINCT temperature) FROM {TABLE}") run(f"SELECT sensor, var_pop(temperature) FROM {TABLE} GROUP BY sensor") run(f"SELECT id FROM {TABLE} WHERE var_pop(temperature) > 1") run(f"SELECT var_pop(temperature) FROM {TABLE} HAVING var_pop(temperature) > 1") run(f"SELECT id, var_pop(temperature) OVER () FROM {TABLE}") run(f"SELECT varp(temperature) FROM {TABLE}") ``` ``` InternalError: Binder Error: No function matches the given name and argument types 'var_pop(DOUBLE, INTEGER)'. You might need to add explicit type casts. 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: Binder Error: WHERE clause cannot contain aggregates! [(3.939999999999999,)] InternalError: The operator 'WINDOW' type=6 is currently not supported. InternalError: Catalog Error: Scalar Function with name varp does not exist! ``` ## Similar functions | Function | Computes | Divides by | One value | | --- | --- | --- | --- | | `var_pop(x)` | Population variance | n | `0.0` | | [var_samp](/develop/docs/dev-database-reference-sql-functions-aggregate-var-samp) (also `variance`) | Sample variance | n − 1 | `NULL` | | [stddev_pop](/develop/docs/dev-database-reference-sql-functions-aggregate-stddev-pop) | Population standard deviation: `sqrt(var_pop(x))` | n | `0.0` | | [stddev_samp](/develop/docs/dev-database-reference-sql-functions-aggregate-stddev-samp) (also `stddev`) | Sample standard deviation | n − 1 | `NULL` | The four on the readings, and on one reading: ``` run(f"SELECT var_pop(temperature), var_samp(temperature), variance(temperature), " f"stddev_pop(temperature), sqrt(var_pop(temperature)), stddev_samp(temperature) FROM {TABLE}") run(f"SELECT var_pop(temperature), var_samp(temperature), stddev_pop(temperature), " f"stddev_samp(temperature) FROM {TABLE} WHERE id = 1") ``` ``` [(3.939999999999999, 4.924999999999999, 4.924999999999999, 1.9849433241279206, 1.9849433241279206, 2.2192341021172144)] [(0.0, None, 0.0, None)] ``` Clean up: ``` run(f"DROP TABLE IF EXISTS {TABLE}") run(f"DROP TABLE IF EXISTS {EXTREMES}") 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. - [FILTER (WHERE ...)](/develop/docs/dev-database-reference-sql-functions-aggregate-filter-clause): restrict one aggregate. - [SELECT](/develop/docs/dev-database-reference-sql-select#aggregates), [EXPLAIN](/develop/docs/dev-database-reference-sql-explain) and [pushdowns](/develop/docs/dev-database-reference-pushdowns).