--- title: "stddev_samp" slug: "dev-database-reference-sql-functions-aggregate-stddev-samp" updated: 2026-10-30T18:18:46Z published: 2026-10-30T18:18:46Z canonical: "docs.vastdata.com/dev-database-reference-sql-functions-aggregate-stddev-samp" --- > ## 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. # stddev_samp *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.* An aggregate function that returns the sample standard deviation of a numeric expression across the rows of a query: how far the values typically are from their mean, dividing by n - 1. `stddev` is the same function. ## Syntax ``` stddev_samp(x) stddev(x) stddev_samp(x) FILTER (WHERE condition) ``` | Argument | Type | Description | | --- | --- | --- | | `x` | A number: `TINYINT`, `SMALLINT`, `INTEGER`, `BIGINT`, `HUGEINT`, `UTINYINT`, `USMALLINT`, `UINTEGER`, `UBIGINT`, `FLOAT`, `DOUBLE` or `DECIMAL`. The values are converted to `DOUBLE`. | The values to measure: a column or an expression. | | `condition` | `BOOLEAN` | Optional. Only the rows where it's true count. See [FILTER](/develop/docs/dev-database-reference-sql-functions-aggregate-filter-clause). | **Returns:** `DOUBLE`. **Aliases:** `stddev`. ## 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_stddev_samp`. 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: seven sensor readings, one of which has no values, and seven special floating-point values (`NaN`, `inf`, `1.0` and values near the `DOUBLE` limits): ``` 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_stddev_samp"' 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" SPECIAL = f"{SCHEMA}.special" run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}") run(f"CREATE TABLE IF NOT EXISTS {TABLE} " "(id BIGINT, sensor VARCHAR, temperature DOUBLE, humidity INTEGER, ok BOOLEAN)") run(f"INSERT INTO {TABLE} VALUES " "(1, 's1', 18.5, 40, true), (2, 's1', 21.0, 45, true), (3, 's2', 19.5, 50, false), " "(4, 's2', 24.0, 55, true), (5, 's3', 22.5, 60, true), (6, 's3', NULL, NULL, NULL), " "(7, 's3', 18.5, 40, true)") run(f"CREATE TABLE IF NOT EXISTS {SPECIAL} (id BIGINT, x DOUBLE)") for row in ["(1, 'NaN'::DOUBLE)", "(2, 'inf'::DOUBLE)", "(3, 1.0)", "(4, 1.0e308)", "(5, 1.0e308)", "(6, 5e-324)", "(7, -1.0e308)"]: run(f"INSERT INTO {SPECIAL} VALUES {row}") ``` ``` [] [] [(7,)] [] [(1,)] [(1,)] [(1,)] [(1,)] [(1,)] [(1,)] [(1,)] ``` ## What it does `stddev_samp(x)` reads `x` on every row the query keeps, skips the NULLs, and computes the sample standard deviation of the n values that remain: 1. the mean of the values; 2. for each value, the square of its difference from the mean; 3. the sum of those squares divided by n - 1 (the sample variance, [var_samp](/develop/docs/dev-database-reference-sql-functions-aggregate-var-samp)); 4. the square root of that. Dividing by n - 1 rather than n is what makes it the *sample* form: use it when the rows are a sample of a larger population. When the rows are the whole population, use [stddev_pop](/develop/docs/dev-database-reference-sql-functions-aggregate-stddev-pop), which divides by n. With one value there's nothing to divide by, so `stddev_samp()` returns NULL where `stddev_pop()` returns `0.0`. Every input type is converted to `DOUBLE`, so integer and `DECIMAL` columns return a `DOUBLE` too. ## NULLs and edge cases | Input | Result | | --- | --- | | Some values NULL | NULLs are skipped | | One value | `NULL` | | One value and a NULL | `NULL` | | Two equal values | `0.0` | | Only NULLs | `NULL` | | No rows | `NULL` | | `stddev_samp(NULL)` | `NULL` | | `stddev_samp(2.5)`, no `FROM` | `NULL`: one value | | `stddev_samp(5)` over a table | `0.0`: the same value on every row | | `1e8 + x` and `1e15 + x` for `x` from 0 to 999 | the same result, `288.8194360957494`: a large offset doesn't change it | | `inf` or `NaN` among the values | Error: `STDDEV_SAMP is out of range!` | | Values near the `DOUBLE` limit, such as `1.0e308` and `-1.0e308` | Error: `STDDEV_SAMP is out of range!` | | `stddev_samp(DISTINCT x)` | Error: `The operator 'UNGROUPED_AGGREGATE' type=8 is currently not supported for this usage.` | | A `BOOLEAN` | Error: `Binder Error: No function matches the given name and argument types 'stddev_samp(BOOLEAN)'. You might need to add explicit type casts.`; cast it, as in `ok::INTEGER` | 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-stddev-samp#set-up-the-examples): ``` run(f"SELECT stddev_samp(temperature), stddev_samp(temperature) FILTER (WHERE id <> 6) FROM {TABLE}") run(f"SELECT stddev_samp(temperature) FROM {TABLE} WHERE id = 1") run(f"SELECT stddev_samp(temperature) FROM {TABLE} WHERE id IN (1, 6)") run(f"SELECT stddev_samp(temperature) FROM {TABLE} WHERE id IN (1, 7)") run(f"SELECT stddev_samp(temperature) FROM {TABLE} WHERE id = 6") run(f"SELECT stddev_samp(temperature) FROM {TABLE} WHERE id > 100") run("SELECT stddev_samp(NULL), stddev_samp(2.5)") run(f"SELECT stddev_samp(5) FROM {TABLE}") run("SELECT stddev_samp(1e8 + x), stddev_samp(1e15 + x) FROM range(1000) t(x)") run(f"SELECT stddev_samp(x) FILTER (WHERE id IN (2, 3)) FROM {SPECIAL}") run(f"SELECT stddev_samp(x) FILTER (WHERE id IN (1, 3)) FROM {SPECIAL}") run(f"SELECT stddev_samp(x) FILTER (WHERE id >= 4) FROM {SPECIAL}") run(f"SELECT stddev_samp(x) FILTER (WHERE id IN (4, 5)) FROM {SPECIAL}") run(f"SELECT stddev_samp(DISTINCT temperature) FROM {TABLE}") run(f"SELECT stddev_samp(ok) FROM {TABLE}") run(f"SELECT stddev_samp(ok::INTEGER) FROM {TABLE}") ``` ``` [(2.250925735484551, 2.250925735484551)] [(None,)] [(None,)] [(0.0,)] [(None,)] [(None,)] [(None, None)] [(0.0,)] [(288.8194360957494, 288.8194360957494)] OSError: STDDEV_SAMP is out of range! OSError: STDDEV_SAMP is out of range! OSError: STDDEV_SAMP is out of range! [(0.0,)] InternalError: The operator 'UNGROUPED_AGGREGATE' type=8 is currently not supported for this usage. InternalError: Binder Error: No function matches the given name and argument types 'stddev_samp(BOOLEAN)'. You might need to add explicit type casts. [(0.408248290463863,)] ``` ## Examples The sample standard deviation of the temperatures, with both names of the function. The empty reading is skipped: ``` run(f"SELECT stddev_samp(temperature), stddev(temperature) FROM {TABLE}") ``` ``` [(2.250925735484551, 2.250925735484551)] ``` An `INTEGER` column returns a `DOUBLE`: ``` run(f"SELECT stddev_samp(humidity) FROM {TABLE}") ``` ``` [(8.16496580927726,)] ``` The mean, the spread and the standard error of the mean in one query. `sem` isn't available, so the standard error is computed as `stddev_samp(x) / sqrt(count(x))`: ``` run(f"SELECT avg(temperature), stddev_samp(temperature), " f"stddev_samp(temperature) / sqrt(count(temperature)) FROM {TABLE}") ``` ``` [(20.666666666666668, 2.250925735484551, 0.9189365834726816)] ``` The spread of each sensor, in one query. `GROUP BY` isn't available, so each sensor gets its own `FILTER` clause: ``` run(f"SELECT stddev_samp(temperature) FILTER (WHERE sensor = 's1') AS s1, " f"stddev_samp(temperature) FILTER (WHERE sensor = 's2') AS s2 FROM {TABLE}") ``` ``` [(1.7677669529663689, 3.181980515339464)] ``` ## How it runs in VAST The Query Engine runs on the cluster's CNodes, and a query on a table starts with a scan in VAST DataBase (`VAST_SCAN`). An aggregate can't go in `WHERE`, so what matters is the plan of the aggregate query: - **The scan** reads the columns the query uses. A `WHERE` condition that VAST DataBase can evaluate, such as a comparison of a column with a constant, is applied in the scan, so the rows it rejects never reach the Query Engine. It doesn't appear as a step in the plan. - **A `WHERE` condition the scan can't evaluate**, such as one on a function of a column, appears as a `FILTER` step above the scan. - **One `UNGROUPED_AGGREGATE` step** in the Query Engine computes `stddev_samp()`, and every other aggregate in the `SELECT` list, over the rows it receives, and returns one row. The plan of a `stddev_samp()` with a pushed-down `WHERE` condition has only the aggregate step and the scan: ``` plan(f"SELECT stddev_samp(temperature) FROM {TABLE} WHERE id > 1") ``` ``` ┌───────────────────────────┐ │ UNGROUPED_AGGREGATE │ │ ──────────────────── │ │ Aggregates: │ │ stddev_samp(temperature) │ └─────────────┬─────────────┘ ┌─────────────┴─────────────┐ │ VAST_SCAN │ │ ──────────────────── │ │ Function: VAST_SCAN │ │ │ │ Projections: │ │ temperature │ │ │ └───────────────────────────┘ ``` ## Make it faster - **Restrict the rows in `WHERE`, not in `FILTER`, when every aggregate needs the same rows.** A `FILTER (WHERE ...)` condition is evaluated in the aggregate step, after the scan has sent every row and the column the condition reads. The plan shows it inside the `UNGROUPED_AGGREGATE` step: ``` plan(f"SELECT stddev_samp(temperature) FILTER (WHERE id > 1) FROM {TABLE}") ``` ``` ┌───────────────────────────┐ │ UNGROUPED_AGGREGATE │ │ ──────────────────── │ │ Aggregates: │ │ stddev_samp(temperature) │ │ FILTER (WHERE (id > 1)) │ │ Filter: (id > 1) │ └─────────────┬─────────────┘ ┌─────────────┴─────────────┐ │ VAST_SCAN │ │ ──────────────────── │ │ Function: VAST_SCAN │ │ │ │ Projections: │ │ id │ │ temperature │ │ │ └───────────────────────────┘ ``` - **Compute the mean, the spread and the count in one query**, as in [Examples](/develop/docs/dev-database-reference-sql-functions-aggregate-stddev-samp#examples). All the aggregates of a query share one scan and one aggregate step. - **Keep functions of a column out of `WHERE` when a plain comparison does the job.** A condition such as `abs(temperature) > 5` adds a `FILTER` step in the Query Engine; `temperature > 5` is applied in the scan. ## Limitations and errors - **Numbers only.** A `BOOLEAN`, `VARCHAR`, `BLOB`, `DATE`, `TIME`, `TIMESTAMP`, `INTERVAL` or list fails with `Binder Error: No function matches the given name and argument types 'stddev_samp(...)'. You might need to add explicit type casts.` Cast a `BOOLEAN` to `INTEGER` first. - **No `DISTINCT` or `ORDER BY` inside the call.** `stddev_samp(DISTINCT x)` and `stddev_samp(x ORDER BY y)` fail with `The operator 'UNGROUPED_AGGREGATE' type=8 is currently not supported for this usage.` - **Infinite, `NaN` and very large values stop the query.** If the values include `inf`, `-inf` or `NaN`, or are so large that the computation overflows `DOUBLE`, the query fails with `STDDEV_SAMP is out of range!`. Leave such rows out in `WHERE`, or scale the values down first. - **No `GROUP BY`.** `SELECT sensor, stddev_samp(temperature) ... GROUP BY sensor` fails with `The operator 'HASH_GROUP_BY' type=9 is currently not supported.` Use one `FILTER` clause per group, as in [Examples](/develop/docs/dev-database-reference-sql-functions-aggregate-stddev-samp#examples), or run the query in [Trino](/develop/docs/dev-database-integrations-trino) or [Spark](/develop/docs/dev-database-integrations-spark). - **As a window function, only `OVER (ROWS UNBOUNDED PRECEDING)` runs**, over the rows in the order the scan returns them. `OVER ()`, `OVER (ORDER BY ...)` and `OVER (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). - **Other SQL dialects' names aren't available.** `std`, `stdev`, `std_dev` and `stddevp` fail with `Catalog Error: Scalar Function with name ... does not exist!`. Use `stddev_samp` or `stddev`, or [stddev_pop](/develop/docs/dev-database-reference-sql-functions-aggregate-stddev-pop) for the population form. ``` run(f"SELECT stddev_samp(temperature ORDER BY id) FROM {TABLE}") run(f"SELECT sensor, stddev_samp(temperature) FROM {TABLE} GROUP BY sensor") run(f"SELECT std(temperature) 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: Catalog Error: Scalar Function with name std does not exist! ``` ## Similar functions | Function | Computes | With one value | | --- | --- | --- | | [stddev_pop](/develop/docs/dev-database-reference-sql-functions-aggregate-stddev-pop) | the population standard deviation: divides by n | `0.0` | | [var_samp](/develop/docs/dev-database-reference-sql-functions-aggregate-var-samp) (also `variance`) | the sample variance: `stddev_samp(x)` squared | `NULL` | | [var_pop](/develop/docs/dev-database-reference-sql-functions-aggregate-var-pop) | the population variance: `stddev_pop(x)` squared | `0.0` | | `sqrt(var_samp(x))` ([sqrt](/develop/docs/dev-database-reference-sql-functions-numeric-sqrt)) | the same as `stddev_samp(x)` | `NULL` | | `max(x) - min(x)` | the range: the distance between the extremes | `0.0` | | [list_stddev_samp](/develop/docs/dev-database-reference-sql-functions-lists-list-stddev-samp) | the sample standard deviation of the elements of a list, per row | | ``` run(f"SELECT stddev_samp(temperature), stddev_pop(temperature), var_samp(temperature), " f"var_pop(temperature), sqrt(var_samp(temperature)), max(temperature) - min(temperature) FROM {TABLE}") run(f"SELECT stddev_samp(temperature), stddev_pop(temperature), var_samp(temperature), " f"var_pop(temperature), sqrt(var_samp(temperature)), max(temperature) - min(temperature) " f"FROM {TABLE} WHERE id = 1") ``` ``` [(2.250925735484551, 2.0548046676563256, 5.066666666666667, 4.222222222222222, 2.250925735484551, 5.5)] [(None, 0.0, None, 0.0, None, 0.0)] ``` Clean up: ``` run(f"DROP TABLE IF EXISTS {TABLE}") run(f"DROP TABLE IF EXISTS {SPECIAL}") 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. - [SELECT](/develop/docs/dev-database-reference-sql-select#aggregates): aggregates in a query, and [EXPLAIN](/develop/docs/dev-database-reference-sql-explain): how to read a plan. - [Pushdowns](/develop/docs/dev-database-reference-pushdowns): which `WHERE` conditions VAST DataBase evaluates.