How the STDDEV_POP() function works in Mariadb?

The STDDEV_POP() function is a statistical aggregate function in MariaDB that computes the population standard deviation of a set of values.

Posted on

The STDDEV_POP() function is a statistical aggregate function in MariaDB that computes the population standard deviation of a set of values. It measures the spread of a data set by calculating how much each data point differs from the mean (average) of the population.

Syntax

The syntax for the MariaDB STDDEV_POP() function is as follows:

STDDEV_POP(expression)

The STDDEV_POP() function takes one argument, expression, which is typically a column name containing numeric data. The function returns the population standard deviation as a double-precision value.

Examples

Example 1: STDDEV_POP() with a Condition

In this example, we calculate the population standard deviation of values that meet a certain condition.

DROP TABLE IF EXISTS students;
CREATE TABLE students (id INT, test_score INT);
INSERT INTO students VALUES (1, 90), (2, 80), (3, 70), (4, 60);

SELECT STDDEV_POP(test_score) FROM students WHERE test_score >= 70;

The output for this statement is:

+------------------------+
| STDDEV_POP(test_score) |
+------------------------+
|                 8.1650 |
+------------------------+

The population standard deviation is calculated for test scores greater than or equal to 70.

Example 2: Grouped STDDEV_POP()

Here, we calculate the population standard deviation for different groups of data.

DROP TABLE IF EXISTS class_scores;
CREATE TABLE class_scores (class_id INT, score INT);
INSERT INTO class_scores VALUES (1, 88), (1, 92), (2, 75), (2, 85);

SELECT class_id, STDDEV_POP(score) FROM class_scores GROUP BY class_id;

The output for this statement is:

+----------+-------------------+
| class_id | STDDEV_POP(score) |
+----------+-------------------+
|        1 |            2.0000 |
|        2 |            5.0000 |
+----------+-------------------+

This shows the population standard deviation of scores for each class.

Below are a few functions related to the MariaDB STDDEV_POP() function:

  • MariaDB AVG() function calculates the average value of a set of values.
  • MariaDB VARIANCE() function computes the population variance of a set of values, which is the square of the population standard deviation.

Conclusion

The STDDEV_POP() function is an essential tool for statistical analysis in MariaDB, providing a measure of the variability within a population. It is crucial for data analysts to understand the difference between population and sample standard deviation (STDDEV_SAMP()) to apply the correct function for their analysis. Accurate use of STDDEV_POP() can help in understanding the distribution and consistency of data across a population.