MySQL STDDEV_POP is one of the Aggregate Function, which calculates the Standard Deviation of total records (or rows) selected by the SELECT Statement.
The mathematical formulas behind the STDDEV_POP to calculate the standard deviation in MySQL is as shown below:
--Calculating the Mean or Average Mean = Sum of each individual/Total number of items --Calculating the Variance Variance = ((OriginalValue – Mean)² + (OriginalValue – Mean)² +.... )/( Total number of items ) --Calculating Standard Deviation Standard Deviation = Square root (Variance)
TIP: The STD and STDDEV Functions are the synonyms for the standard STDDEV_POP function. So, you can use any of these function names
The basic syntax behind the STD, STDDEV, STDDEV_POP in MySQL is as shown below:
-- MySQL STD example SELECT STD(Column_Name) FROM Source; -- MySQL STDDEV example SELECT STDDEV(Column_Name) FROM Source; -- MySQL STDDEV_POP example SELECT STDDEV_POP(Column_Name) FROM Source;
In this article, we show you, How to write STD, STDDEV, STDDEV_POP functions in MySQL to calculate the Standard Deviation with example. For this, We are going to use the below shown data
MySQL STDDEV_POP Example
In MySQL, STD, STDDEV, and STDDEV_POP functions simply return the Standard Deviation of the total number of records present in the specified column. For example, the below query calculate the Standard Deviation of total records present in Yearly_Income column from
-- MySQL STD example SELECT STD(Yearly_Income) AS `Standard income` FROM customerdetails; -- MySQL STDDEV example SELECT STDDEV(Yearly_Income) AS `Standard income` FROM customerdetails; -- MySQL STDDEV_POP example SELECT STDDEV_POP(Yearly_Income) AS `Standard income` FROM customerdetails;
MySQL STDDEV_POP Group By Example
In general, we use this STDDEV_POP function to calculate the Standard Deviation of products belongs to a particular category or color, etc.
In these situations, we can use GROUP BY Clause to group the products by color or category. And then, we use STDDEV_POP Function to calculate the Standard Deviation of products present in each group. Let us see the Aggregate Function Example.
-- MySQL STDDEV_POP Function Example USE company; SELECT Profession, STDDEV_POP(Yearly_Income), STDDEV(Yearly_Income), STD(Yearly_Income) FROM customerdetails GROUP BY Profession;
Above MySQL Query group the Customers by their Profession, and calculates their Standard Deviation
We are taking the Software Developer profession, and show you the SELECT Statement output.
Mean = (70000 + 79000) / 2
Mean = 74500
Variance = ( (70000 – 74500)² + (79000 – 74500) )² / 2
Variance = 20,250,000
–Calculating Standard Deviation
Standard Deviation = SQRT (Variance)
Standard Deviation = SQRT (20,250,000)
which means, Standard Deviation = 4500