The SQL STDEVP Function is one of the SQL Aggregate Function, which is used to calculate the standard Deviation for population of total records (or rows) selected by the SELECT Statement.

**TIP:** STDEVP Function will only work on Numeric Columns and it ignores Nulls.

## SQL STDEVP Function Formulas

The mathematical formulas behind the STDEVP Function in SQL Server is as shown below

1 2 3 4 5 6 7 8 |
--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 basic syntax of the STDEVP Function in SQL Server is as shown below:

1 2 |
SELECT STDEVP ([Column_Name]) FROM [Source] |

In this article we will show you, How to write STDEVP Function in SQL Server 2014 to calculate the Standard Deviation for population with example. For this, We are going to use the below shown data

## SQL STDEVP Example

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

**T-SQL CODE**

1 2 3 4 |
-- Example for SQL Server STDEVP Function SELECT STDEVP ([YearlyIncome]) AS [Standard Deviation] FROM [Customer] |

**OUTPUT**

## SQL STDEVP Function with Group By Clause

In most cases we usually calculate the Standard Deviation for population of a products belongs to particular category or color etc. In these situations we use SQL GROUP BY Clause to group the products by color or category and then use the STDEV Function to calculate the Standard Deviation of products present in each group. Let us see the Example

**T-SQL CODE**

1 2 3 4 5 |
-- Example for SQL Server STDEVP Function SELECT [Occupation] ,STDEVP ([YearlyIncome]) AS [Standard Deviation] FROM [Customer] GROUP BY [Occupation] |

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

**OUTPUT**

**ANALYSIS**

It is too big to show the calculations for every thing so, We are taking Skilled Manual profession and show you the output.

1 2 3 4 5 6 7 8 9 10 11 12 |
--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 |

## SQL STDEVP Function in Having Clause

When we are grouping the data, In some cases we usually check for the conditions against the aggregated data. In these situations we use SQL HAVING Clause along with Group By Statement. For example, The following query will group the Customers by their Occupation and then calculates the Standard Deviation for population of each individual group.

**T-SQL CODE**

1 2 3 4 5 6 |
-- Example for SQL Server STDEVP Function SELECT [Occupation] ,STDEVP ([YearlyIncome]) AS [Standard Deviation] FROM [Customer] GROUP BY [Occupation] HAVING STDEVP ([YearlyIncome]) > 0 |

Below lines of code will check whether the aggregated amount (Standard Deviation of Yearly income for each individual Group) is greater than 0 or not. If this is True then corresponding records will be displayed.

1 |
HAVING STDEVP ([YearlyIncome]) > 0 |

**OUTPUT**

## SQL STDEVP Function in Sub Query

When we are retrieving the data, In some cases we usually check for the conditions against the aggregated data. In these situations we have to use Sub queries in SQL Where Clause. For example, The following query will return all the Customers present in the customers table whose [Yearly Income] is greater than five times yearly incomes Standard Deviation for population.

**T-SQL CODE**

1 2 3 4 5 6 7 8 9 |
-- Example for SQL Server 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, 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

**OUTPUT**

Thank You for Visiting Our Blog

## Leave a Reply