MySQL STDDEV_SAMP Function

MySQL STDDEV_SAMP is one of the Aggregate Functions, which is useful to calculate the Sample Standard Deviation of total records (or rows) selected by the SELECT Statement.

The Mathematical formula behind STDDEV_SAMP to calculate the sample standard deviation is

--Calculating the Mean or Average
Mean = Sum of each individual / Total number of items

--Calculating the Sample Variance
Sample Variance = ((OriginalValue – Mean)² + (OriginalValue – Mean)² +.... )/( Total number of items - 1)

--Calculating Sample Standard Deviation
Sample Standard Deviation = Square root (Sample Variance)

The basic syntax behind the STDDEV_SAMP in MySQL is as shown below:

-- example
SELECT STDDEV_SAMP(Column_Name)
FROM Source;

How to write the STDDEV_SAMP function to calculate Sample Standard Deviation with an example using the below shown data?

Table 1

MySQL STDDEV_SAMP Example

The STDDEV_SAMP function returns Sample Standard Deviation of total records present in a specified column. 

For example, the below STDDEV_SAMP query calculate the Sample Standard Deviation of total records present in the Yearly_Income from the Customer details table. Also, read about the MySQL STDDEV_POP.

SELECT STDDEV_SAMP(Yearly_Income) AS `Sample Standard Deviation`
FROM customerdetails;
STDDEV_SAMP Example 1

STDDEV_SAMP Group By Example

In general, we use the STDDEV_SAMP function to calculate the Sample Standard Deviation of products belonging to a particular category or color, etc.

In this situation, we use MySQL GROUP BY Clause to group the products by color or something like that. Then, we use this STDDEV_SAMP Function to calculate the Sample Standard Deviation. Let us see the MySQL GROUP BY Example

USE company;
SELECT  Profession,
        STDDEV_SAMP(Yearly_Income),
FROM customerdetails
GROUP BY Profession;

The above MySQL SELECT STDDEV_SAMP Query groups the Customers by their Profession and calculates their Sample Standard Deviation.

TIP: To get the variance, please refer to the MySQL VAR_POP and MySQL VAR_SAMP articles.

MySQL STDDEV_SAMP Function Example 2

For these MySQL Aggregate Functions demonstration, we are taking the Software Developer profession. And it shows you the STDDEV_SAMP output.

— Calculating the Mean
Mean = (70,000 + 79000) / 2
Mean = 74,500

— Calculating the Sample Variance
Sample Variance = ( (70,000 – 74500)² + (79000 – 74500) )² / (2 -1)
Sample Variance = 40,500,000

— Calculating Sample Standard Deviation
Sample Standard Deviation = SQRT (Variance)
Sample Standard Deviation = SQRT (40,500,000)
That means Sample Standard Deviation = 6363.96