How the STDDEV_SAMP() function works in Mariadb?

The STDDEV_SAMP() function in MariaDB is a statistical function that calculates the sample standard deviation of a set of values.

Posted on

The STDDEV_SAMP() function in MariaDB is a statistical function that calculates the sample standard deviation of a set of values. Unlike the population standard deviation, which includes all possible values, the sample standard deviation is calculated using a sample of the population, which can be a subset of the entire data set.

Syntax

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

STDDEV_SAMP(expression)

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

Examples

Example 1: STDDEV_SAMP() with a Condition

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

DROP TABLE IF EXISTS student_grades;
CREATE TABLE student_grades (grade INT);
INSERT INTO student_grades VALUES (65), (70), (75), (80), (85);

SELECT STDDEV_SAMP(grade) FROM student_grades WHERE grade >= 70;

The output for this statement is:

+--------------------+
| STDDEV_SAMP(grade) |
+--------------------+
|             6.4550 |
+--------------------+

The sample standard deviation is calculated for grades greater than or equal to 70.

Example 2: Grouped STDDEV_SAMP()

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

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

SELECT class_id, STDDEV_SAMP(grade) FROM class_grades GROUP BY class_id;

The output for this statement is:

+----------+--------------------+
| class_id | STDDEV_SAMP(grade) |
+----------+--------------------+
|        1 |             2.8284 |
|        2 |             7.0711 |
+----------+--------------------+

This shows the sample standard deviation of grades for each class.

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

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

Conclusion

The STDDEV_SAMP() function is an important tool for statistical analysis in MariaDB when dealing with samples of a population. It provides a measure of how much the values in the sample deviate from the sample mean. Understanding the distinction between sample and population standard deviation is crucial for accurate statistical analysis and interpretation of data variability.