SQL STDEV Function

How to write STDEV in SQL Server to calculate the Standard Deviation with an example?. The STDEV Function is an Aggregate Function, which is used to calculate the Standard Deviation of total records (or rows) selected by the SELECT Statement.

The STDEV Function works only on Numeric Columns and ignores Nulls. The syntax of the STDEV is mentioned below.

SELECT STDEV ([Column_Name])
FROM [Source]

SQL STDEV Function Formula

The math formula of the STDEV function to calculate the standard deviation is shown below.

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

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

For this STDDEV standard deviation, We use the following data

SQL STDEV function to find Standard Deviation

The STDEV function returns the Standard Deviation of the total number of records present in the specified column. For example, the following query will calculate the Standard Deviation of total records present in the [Yearly Income] column from the Customers table.

-- STDEV Function example
SELECT STDEV ([YearlyIncome]) AS [Standard Deviation]    
  
  FROM [Customer]
STDEV FUNCTION 1

STDEV Function with Group By Clause

In general, we calculate the Standard Deviation of products belonging to a category or color, etc. In this case, we use the GROUP BY Clause to group the products by color or category. Next, use the STDEV Function to calculate the Standard Deviation of products present in each group.

SELECT [Occupation]
       ,STDEV ([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

SQL STDEV FUNCTION in Group By Clause 2

We use the Skilled Manual profession and show you the SQL Server output.

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

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

--Calculating Standard Deviation
Standard Deviation = SQRT (Variance)
Standard Deviation = SQRT (200000000)
which means, Standard Deviation = 14142.13

SQL STDEV Function in Having Clause

When we perform data grouping, In a few instances, we check for the conditions against the aggregated data. If this is the case, use SQL HAVING Clause along with Group By Statement.

For example, the SQL Server standard deviation query groups the Customers by their Occupation. And then, it calculates the Standard Deviation of each group.

SELECT [Occupation]
       ,STDEV ([YearlyIncome]) AS [Standard Deviation]    
 FROM [Customer]
 GROUP BY [Occupation]
 HAVING STDEV ([YearlyIncome]) > 0

The below code checks whether the Standard Deviation of Yearly income for each individual Group is greater than 0 or not. If this is True, the corresponding records will be displayed.

HAVING STDEV ([YearlyIncome]) > 0
STDEV FUNCTION in Having Clause 3

SQL STDEV Function in Subquery

How to calculate standard Deviation in Sql Server using the Subqueries in Where Clause. For example, the following STDEV query returns all the Customers in the customer’s table whose [Yearly Income] is greater than five times yearly income Standard Deviation.

SELECT [FirstName]
      ,[LastName]
      ,[YearlyIncome]
      ,[Education]
      ,[Occupation]
  FROM [Customer]
  WHERE [YearlyIncome] > 
        (SELECT STDEV([YearlyIncome] * 5) FROM [Customer])

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

Categories SQL