How to write STDEV in SQL Server to calculate the Standard Deviation with an example?. The SQL 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 in SQL Server is
SELECT STDEV ([Column_Name]) FROM [Source]
SQL STDEV Function Formula
The math formula of the STDEV function to calculate the standard deviation in SQL Server 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 - 1) --Calculating Standard Deviation Standard Deviation = Square root (Variance)
For this STDDEV standard deviation, We use the following data
SQL STDEV Example
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.
-- SQL Server STDEV Function example SELECT STDEV ([YearlyIncome]) AS [Standard Deviation] FROM [Customer]
SQL 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 SQL 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.
-- SQL Server STDEV Function example 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
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 group the Customers by their Occupation. And then, it calculates the Standard Deviation of each group.
-- SQL Server STDEV Function example 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
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 incomes Standard Deviation in SQL Server.
-- SQL Server STDEV Function example 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