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 as shown below:
--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 in MySQL 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 Yearly_Income from Customer details table.
-- MySQL STDDEV_SAMP example SELECT STDDEV_SAMP(Yearly_Income) AS `Sample Standard Deviation` FROM customerdetails;
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 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 Example
-- MySQL STDDEV_SAMP Function Example USE company; SELECT Profession, STDDEV_SAMP(Yearly_Income), FROM customerdetails GROUP BY Profession;
The above STDDEV_SAMP Query group the Customers by their Profession, and calculates their Sample Standard Deviation
For this 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