MySQL STDDEV_SAMP Function

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

MySQL STDDEV_SAMP Formula

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)

MySQL STDDEV_SAMP Syntax

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

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

MySQL STDDEV_SAMP Group By Example

In general, we use the 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 show you the STDDEV_SAMP output.

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

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

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