SQL STDEVP Function

The SQL STDEVP Function is an Aggregate Function that calculates the Standard Deviation for the population of total records (or rows) selected by the SELECT Statement. The syntax of the SQL Server STDEVP Function is

SELECT STDEVP ([Column_Name])
FROM [Source]

SQL STDEVP Function Formulas

SQL STDEVP Function works on Numeric values, and it ignores Nulls. The math formulas of the SQL Server STDEVP Function is

--Calculating the Mean or Average
Mean = Sum of each individual/Total number of items

--Calculating the Statistical Variance
Variance = ((OriginalValue – Mean)² + (OriginalValue – Mean)² +.... ) / Total number of items

--Calculating Standard Deviation
Standard Deviation = Square root (Variance)

SQL STDEVP Function Syntax

The syntax of the STDEVP Function is

SELECT STDEVP ([Column_Name])
FROM [Source]

For this SQL Server STDDEVP function to calculate the Standard Deviation for population example, we use the below-shown data

SQL STDEVP FUNCTION

SQL STDEVP Example

The STDEVP function returns the Standard Deviation for the population of a total number of records present in the specified column. For example, the following STDEVP query calculates the Standard Deviation for the population of total records present in the [Yearly Income] column.

-- Example for STDEVP Function
SELECT STDEVP ([YearlyIncome]) AS [Standard Deviation]    
  
  FROM [Customer]
SQL STDEVP FUNCTION 1

SQL STDEVP Function with Group By Clause

We use the STDEV Function to calculate the Standard Deviation of products present in each group using the GROUP BY Clause. Let us see the Aggregate Function Example

-- Example for STDEVP Function
SELECT [Occupation]
       ,STDEVP ([YearlyIncome]) AS [Standard Deviation]    
  FROM [Customer]
  GROUP BY [Occupation]

Above SQL Select Statement Query will find the Customers associated with the particular Department and calculates their Standard Deviation for the population.

SQL STDEVP FUNCTION

Let us take the Skilled Manual profession and show you the output.

--Calculating Mean
Mean = (60000 + 80000) / 2
Mean = 70000

--Calculating Variance
Variance = ( (60000 - 70000) + (80000 - 70000) ) / 2
Variance = 100000000

--Calculating Standard Deviation
Standard Deviation = SQRT (Variance)
Standard Deviation = SQRT (100000000)
It means, Standard Deviation for Population = 10000

STDEVP Function in Having Clause

The following STDEVP query groups the Customers by their Occupation and then calculates the Standard Deviation for the population of each group. Next, use the SQL HAVING Clause to return rows whose aggregated data is greater than 0

-- Example for STDEVP Function
SELECT [Occupation]
       ,STDEVP ([YearlyIncome]) AS [Standard Deviation]    
 FROM [Customer]
 GROUP BY [Occupation]
 HAVING STDEVP ([YearlyIncome]) > 0

The below code checks whether the Standard Deviation for the population of Yearly income for each Group is greater than 0 or not. If this is true, the corresponding records will display.

HAVING STDEVP ([YearlyIncome]) > 0
SQL STDEVP FUNCTION 3

SQL STDEVP Function in Subquery

When we are retrieving the data, In some cases, we check for the conditions against the aggregated data. In these conditions, we have to use Subquery in SQL Where Clause.

For example, the following STDEVP query returns Customers whose Income is five times greater than the yearly incomes Standard Deviation for the population.

-- Example for STDEVP Function
SELECT [FirstName]
      ,[LastName]
      ,[YearlyIncome]
      ,[Education]
      ,[Occupation]
  FROM [Customer]
  WHERE [YearlyIncome] > 
        (SELECT STDEVP([YearlyIncome] * 6) FROM [Customer])

From the first example, you can observe that the Standard Deviation of a Yearly Income is 11874. So above query will display all the customers from the Customers table whose yearly income is greater than 71244 approximately

SQL STDEVP FUNCTION 4
Categories SQL