InterSystems SQL Reference
STDDEV, STDDEV_SAMP, STDDEV_POP
Aggregate functions that return the statistical standard deviation of a data set.
STDDEV([ALL | DISTINCT [BY(col-list)]] expression [%FOREACH(col-list)] [%AFTERHAVING])
STDDEV_SAMP([ALL | DISTINCT [BY(col-list)]] expression [%FOREACH(col-list)] [%AFTERHAVING])
STDDEV_POP([ALL | DISTINCT [BY(col-list)]] expression [%FOREACH(col-list)] [%AFTERHAVING])
||Optional Specifies that standard deviation functions return the standard deviation of all values for expression. This is the default if no keyword is specified.
||Optional A DISTINCT clause that specifies that standard deviation functions return the standard deviation of the distinct (unique) expression values. DISTINCT can specify a BY(col-list) subclause, where col-list can be a single field or a comma-separated list of fields.
||Any valid expression. Usually the name of a column that contains the data values to be analyzed for standard deviation.
||Optional A column name or a comma-separated list of column names. See SELECT for further information on %FOREACH.
||Optional Applies the condition found in the HAVING clause.
These three standard deviation aggregate functions
return the statistical standard deviation of the distribution of the values of expression
, after discarding NULL values. That is, the amount of standard deviation from the mean value of the data set, expressed as a positive number. The larger the return value, the more variation there is within the data set of values.
(sample), and STDDEV_POP
(population) functions are derived from the corresponding variance aggregate functions:
The standard deviation is the square root of the corresponding variance value. Refer to these variance aggregate functions
for further details.
These standard deviation functions can be used in a SELECT
query or subquery that references either a table or a view. They can appear in a SELECT
list or HAVING
clause alongside ordinary field values.
These standard deviation functions cannot be used in a WHERE
clause. They cannot be used in the ON
clause of a JOIN
, unless the SELECT
is a subquery.
These standard deviation functions return a value of data type NUMERIC with a precision of 36 and a scale of 17, unless expression
is data type DOUBLE in which case it returns data type DOUBLE.
These functions are normally applied to a field or expression that has a numeric value. They evaluate nonnumeric values, including the empty string (''), as zero (0).
These standard deviation functions ignore NULL values in data fields. If no rows are returned by the query, or the data field value for all rows returned is NULL, they return NULL.
The standard deviation functions, like all aggregate functions, can take an optional DISTINCT clause
. STDDEV(DISTINCT col1)
returns the standard deviation of those col1 field values that are distinct (unique). STDDEV(DISTINCT BY(col2) col1)
returns the standard deviation of the col1 field values in records where the col2 values are distinct (unique). Note however that the distinct col2 values may include a single NULL as a distinct value.
Changes Made During the Current Transaction
Like all aggregate functions, standard deviation functions always returns the current state of the data, including uncommitted changes, regardless of the current transaction’s isolation level. For further details, refer to SET TRANSACTION
and START TRANSACTION
The following example uses STDDEV
to return the standard deviation in the ages of the employees in Sample.Employee, and the standard deviation in the distinct ages represented by one or more employees:
SELECT STDDEV(Age) AS AgeSD,STDDEV(DISTINCT Age) AS PerAgeSD
The following example uses STDDEV_POP
to return the population standard deviation in the ages of the employees in Sample.Employee, and the standard deviation in the distinct ages represented by one or more employees:
SELECT STDDEV_POP(Age) AS AgePopSD,STDDEV_POP(DISTINCT Age) AS PerAgePopSD