Aggregate: Standard Deviation (stdev)

[Akshay1]

Estimates the standard deviation (how much variation from the average) exists within a sample set of data. This aggregate function calculates the standard deviation of the numeric values in the reference column (the column to which stdev is applied) among those rows that are identical.

All duplicate rows are discovered by conducting a column-by-column examination (excluding the column to which stdev is being applied) before they are collapsed into unique single rows. For each resulting single row, the value in the reference column from the contributing duplicate row becomes part of the standard deviation calculation. The header of the reference column receives a name change to become “Stdev of <column name>”.

If there are text values in the reference column, they will be ignored within the stdev calculation. Note also that the aggregate stdev function requires at least two values. In other words, there must be at least two identical rows available for aggregation for each unique single row that is returned. Rows that occur only once will contribute only one value in the reference column for the calculation—resulting in an error.

The standard deviation for data is the square root of its var. If the set under analysis represents all data points (referred to as a “population”) use of stdevp is recommended for a more accurate result. A related function that deals with statistical variance is varp.

Example

The dataset below will be used to show how stdev operates. All numbers are numeric values (not text) in order to illustrate behavior of this aggregate function.

Column A	Column B	Column C
one	two	0.2
one	two	0.1
one	two	1.1
one	two	0.2
one	two	0.6
one	one	0.2
one	one	0.27
one	two	0.2
one	two	0.4

As shown in the table below, applying the stdev function to Column C reduces the row count from nine to two. The value in the column Stdev of Column C shows the standard deviation of the Column C sample data values in the duplicate rows that were collapsed during the operation.

Column A	Column B	Stdev of Column C
one	two	0.3511884584284246
one	one	0.049497474683058325