SQL Server Tutorial SQL Server Advanced SQL Server Database SQL Server References

SQL Server STDEVP() Function



The SQL Server (Transact-SQL) STDEVP() function returns the statistical standard deviation for the population for all values in the specified expression.

Syntax

The syntax for using STDEVP() function is given below:

SELECT STDEVP(column_name) 
FROM table_name
WHERE condition(s);

The SQL Server (Transact-SQL) GROUP BY clause is used to arrange result table into identical groups when one or more columns are used. Please note that it is must to include those column names in a GROUP BY clause which are not encapsulated within the STDEVP() function. See the syntax below:

SELECT column1, column2, ...
       STDEVP(column_name) 
FROM table_name
WHERE condition(s)
GROUP BY column1, column2, ...;

Parameters

column1, column2, ... Specify the column names that are not encapsulated within the STDEVP() function. It must be included in the GROUP BY clause.
column_name Specify the column or expression whose standard deviation for the population need to be returned.
table_name Specify the table name from where the records need to retrieved.
WHERE condition(s) Optional. Specify the condition(s). Records are selected based upon specified condition(s).

Return Value

Returns the statistical standard deviation for the population for all values in the specified expression.

Example - With Single Column

Consider a database table called Employee with the following records:

EmpIDNameCityAgeSalary
1JohnLondon253000
2MarryNew York242750
3JoParis272800
4KimAmsterdam303100
5RameshNew Delhi283000
6HuangBeijing282800

To get the standard deviation of salary of all employees whose age is greater than 25, the following query can be used:

SELECT STDEVP(Salary) AS STDEVP_Salary 
FROM Employee
WHERE Age > 25;

This will produce the result as shown below:

STDEVP_Salary
129.9038105676658


Example - Using DISTINCT

The DISTICT clause can be used with STDEVP() function. For example - To get the standard deviation of distinct (unique) salaries, the following code can be used:

SELECT STDEVP(DISTINCT Salary) AS STDEVP_UniqueSalary 
FROM Employee;

This will produce the result as shown below:

STDEVP_UniqueSalary
143.06903927824496


Example - Using Formula

The expression contained within the STDEVP() function does not need to be a single field. A formula can also be used with this function. For example - Consider a bonus (15% of Salary) is given to each employee, and to calculate the standard deviation of bonus amount, the following query can be used:

SELECT STDEVP(Salary * 0.15) AS STDEVP_BonusAmount
FROM Employee;

This will produce the result as shown below:

STDEVP_BonusAmount
19.565594803123158


Example - Using GROUP BY

To get the standard deviation of salary of employees group by their age, the following query can be used:

SELECT Age, STDEVP(Salary) AS STDEVP_SalaryByAge 
FROM Employee
GROUP BY Age;

This result of the above code will be:

AgeSTDEVP_SalaryByAge
240
250
270
28100
300

❮ SQL Server Functions