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