# VARIANCE()

## USAGE

```VARIANCE(number)
```

Argument Name

Description

`number`

The number to be aggregated

See VAR_POP().

## EXAMPLES

### Example Schema and Data

All of the examples are based on the `inventory` table:

```CREATE TABLE inventory (
item VARCHAR(20),
category VARCHAR(20),
count INT
);
```
```INSERT INTO inventory VALUES
('Foo','in stock', 42), ('Foo','back order', 50),
('Bar','in stock', 10), ('Bar','back order', 60),
('Baz','in stock', 99), ('Baz','back order', 10),
('FooBar','in stock', 10), ('FooBar','back order', NULL),
('BarBaz','in stock', NULL), ('BarBaz','back order', NULL);
```

The values to be computed:

```SELECT item, count
FROM inventory;
```
```+--------+-------+
| item   | count |
+--------+-------+
| Foo    |    42 |
| Foo    |    50 |
| Bar    |    10 |
| Bar    |    60 |
| Baz    |    99 |
| Baz    |    10 |
| FooBar |    10 |
| FooBar |  NULL |
| BarBaz |  NULL |
| BarBaz |  NULL |
+--------+-------+
```

### Default Behavior

The result of computing population variance over these item counts:

```SELECT VARIANCE(count)
FROM inventory;
```
```+-----------------+
| VARIANCE(count) |
+-----------------+
|    954.97959184 |
+-----------------+
```

### With GROUP BY

Using `VARIANCE()` with `GROUP BY` creates a separate population variance for each group. In this example, `GROUP BY category` creates a separate population variance for each unique category:

```SELECT category, VARIANCE(count)
FROM inventory
GROUP BY category
ORDER BY category;
```
```+------------+-----------------+
| category   | VARIANCE(count) |
+------------+-----------------+
| back order |    466.66666667 |
| in stock   |   1321.18750000 |
+------------+-----------------+
```

### With Only NULL Values

When `VARIANCE()` is acting only on `NULL` values, the return value is `NULL`:

```SELECT VARIANCE(count)
FROM inventory
WHERE item = 'BarBaz';
```
```+-----------------+
| VARIANCE(count) |
+-----------------+
|            NULL |
+-----------------+
```

## CHANGE HISTORY

Release Series

History

23.09

• Present starting in MariaDB Xpand 23.09.1.

6.1

• Present starting in MariaDB Xpand 6.1.0.

6.0

• Present starting in MariaDB Xpand 6.0.3.

5.3

• Present starting in MariaDB Xpand 5.3.13.

Release Series

History

6.0

• Present starting in MariaDB Xpand 6.0.3.

5.3

• Present starting in MariaDB Xpand 5.3.13.

Release Series

History

6.1

• Present starting in MariaDB Xpand 6.1.0.