Oracle STDDEV_SAMP() Function

The Oracle (PL/SQL) STDDEV_SAMP() function returns the statistical standard deviation of all values in the specified expression.

This function differs from STDDEV() in that STDDEV() returns zero when it has only 1 row of input data, whereas STDDEV_SAMP() returns null.

Syntax

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

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

The Oracle (PL/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 STDDEV_SAMP() function. See the syntax below:

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

Parameters

Return Value

Returns the statistical standard deviation of all values in the specified expression.

Example - With Single Column

Consider a database table called Employee with the following records:

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

SELECT STDDEV_SAMP(Salary) AS STDDEV_SAMP_Salary 
FROM Employee
WHERE Age > 25;

This will produce the result as shown below:

Example - Using Formula

The expression contained within the STDDEV_SAMP() 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 STDDEV_SAMP(Salary * 0.15) AS STDDEV_SAMP_BonusAmount
FROM Employee;

This will produce the result as shown below:

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, STDDEV_SAMP(Salary) AS STDDEV_SAMP_SalaryByAge 
FROM Employee
GROUP BY Age;

This result of the above code will be: