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

# VARIANCE

> Returns sample variance.

<div className="e6-meta"><span className="e6-eyebrow e6-eyebrow--quiet">Aggregate · Window</span></div>

**Aliases:** [`var_samp`](/query/sql/functions/aggregate/var_samp)

NULL values are ignored. Fewer than two non-NULL values yields NULL.

## Syntax

A window function keeps each input row and adds a result for that row. `PARTITION BY` groups rows with matching values; without it, all input rows form one group.

### Aggregate

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
variance(value)
```

### Window

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
variance(value) OVER ([PARTITION BY key] [ORDER BY ordering_key] [ROWS frame])
```

## Arguments

### Aggregate and window

| Name | Type | Required | Description |
| - | - | - | - |
| value | TINYINT, SMALLINT, INTEGER, BIGINT, FLOAT, DOUBLE or DECIMAL(p, s) | Yes | Non-NULL numeric inputs. For DECIMAL(p, s), p is precision (total digits) and s is scale (digits after the decimal point). |

## Returns

* Type: DOUBLE
* NULL behavior: NULL values are ignored. Fewer than two non-NULL values yields NULL.
* Empty input: Returns NULL on empty input.

## Properties

| Property | Value |
| - | - |
| Deterministic | Partial (Floating-point results can differ slightly because of rounding.) |
| Supports DISTINCT | Partial (Removes duplicates in grouped aggregation; window calls still include duplicates.) |
| Supports window usage | Partial (Sliding ROWS frames work without DISTINCT. RANGE offsets are not supported.) |
| Supports window frames | Partial (Sliding ROWS frames are supported. RANGE offsets are not supported.) |
| Exact result | Partial (Floating-point arithmetic can round.) |

## Usage notes

* Returns DOUBLE. Sample statistics divide by n − 1; population statistics divide by n, where n is the number of included values.
* INTEGER and INT mean the same type; STRING means VARCHAR text. NULL can be used with a supported type. Cast NULL explicitly when its type would otherwise be unclear.
* DISTINCT removes duplicates in grouped aggregation. Do not use it to remove duplicates in a window call.

## Examples

### Sample variance

The squared distances from the mean are 4, 0 and 4. Dividing their sum by 3 − 1 gives a sample variance of 4.

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
WITH t(v) AS (VALUES (1.0), (3.0), (5.0)) SELECT variance(v) AS result FROM t;
```

| result |
| - |
| 4.0 |

### Groups with different numbers of values

NULL values are excluded from the calculation. Results are rounded to six decimal places where needed.

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
WITH sample(grp, v) AS (VALUES (1, 1), (1, 5), (2, 8), (2, CAST(NULL AS INT)))
SELECT grp, round(variance(CAST(v AS DOUBLE)), 6) AS result
FROM sample GROUP BY grp ORDER BY grp;
```

| grp | result |
| - | - |
| 1 | 8 |
| 2 | NULL |

## Limitations

* DISTINCT removes duplicates in grouped calls.
* The result is DOUBLE. Query tools may report the input’s floating-point or decimal type, but that does not change the returned value’s type.
* DISTINCT in a window call currently has no effect: duplicate rows still contribute to the result. Use grouped DISTINCT aggregation when duplicate removal is required.


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