VAR_POP() - Population Standard Variance

Q

How to calculate the population standard variance of a field expression in result set groups using the VAR_POP() function?

✍: FYIcenter.com

A

VAR_POP(expr) is a MySQL built-in aggregate function that calculates the population standard variance of a field expression in result set groups. For example:

```SELECT help_category_id, VAR_POP(help_topic_id), COUNT(help_topic_id)
FROM mysql.help_topic GROUP BY help_category_id;
-- +------------------+------------------------+----------------------+
-- | help_category_id | VAR_POP(help_topic_id) | COUNT(help_topic_id) |
-- +------------------+------------------------+----------------------+
-- |                1 |                   0.25 |                    2 |
-- |                2 |     105.16408163265307 |                   35 |
-- |                3 |      7175.054869290432 |                   59 |
-- |                4 |                   0.25 |                    2 |
-- |                5 |      2.888888888888887 |                    3 |
-- ...
-- +------------------+------------------------+----------------------+

SELECT help_category_id, help_topic_id
FROM mysql.help_topic WHERE help_category_id = 5;
-- +------------------+---------------+
-- | help_category_id | help_topic_id |
-- +------------------+---------------+
-- |                5 |            40 |
-- |                5 |            43 |
-- |                5 |            44 |
-- +------------------+---------------+
```

VAR_POP() is also a window function, you can call it with the OVER clause to calculate the population standard variance of the given expression in the current window. For example:

```SELECT help_topic_id, help_category_id,
VAR_POP(help_topic_id) OVER w
FROM mysql.help_topic
WINDOW w AS (PARTITION BY help_category_id);
-- +---------------+------------------+-------------------------------+
-- | help_topic_id | help_category_id | VAR_POP(help_topic_id) OVER w |
-- +---------------+------------------+-------------------------------+
-- |             0 |                1 |                          0.25 |
-- |             1 |                1 |                          0.25 |
-- |             2 |                2 |            105.16408163265307 |
-- |             6 |                2 |            105.16408163265307 |
-- |             7 |                2 |            105.16408163265307 |
-- |             8 |                2 |            105.16408163265307 |
-- |             9 |                2 |            105.16408163265307 |
-- ...
-- +---------------+------------------+-------------------------------+
```

Reference information of the VAR_POP() function:

```VAR_POP(expr): var
Returns the population standard variance of expr. It considers rows as
the whole population, not as a sample, so it has the number of rows as
the denominator. If there are no matching rows, VAR_POP() returns NULL.

Arguments, return value and availability:
expr: Required. The field expression in result set groups.
var: Return value. The population standard variance of the input expression.
Available since MySQL 5.7.
```

Related MySQL functions:

2023-12-01, 328🔥, 0💬