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 in MySQL 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:

-- STDDEV_SAMP example
SELECT STDDEV_SAMP(Column_Name)
FROM Source;

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

MySQL STDDEV_SAMP Example 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 Yearly_Income from Customer details table.

-- MySQL STDDEV_SAMP example
SELECT STDDEV_SAMP(Yearly_Income) AS `Sample Standard Deviation`
FROM customerdetails;
MySQL STDDEV_SAMP Function Example 1

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

-- MySQL STDDEV_SAMP Function 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

MySQL STDDEV_SAMP Function Example 2

For this Aggregate Function demonstration, we are taking the Software Developer profession.

And it shows you the MySQL 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