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?

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.

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

STDDEV_SAMP Group By Example

In general, we use the MySQL STDDEV_SAMP function to calculate the Sample Standard Deviation of products belongs 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. And then, we use this STDDEV_SAMP Function to calculate the Sample Standard Deviation. Let us see the Group By Example

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

The above STDDEV_SAMP SELECT Query group the Customers by their Profession and calculates their Sample Standard Deviation

For this Aggregate Function 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)
which means Sample Standard Deviation = 6363.96