STDDEV_SAMP function [Aggregate]

STDDEV_SAMP( numeric-expression )

STDDEV_SAMP( numeric-expression ) OVER ( window-spec )

window-spec : see Syntax 2 instructions in the Remarks section below

numeric-expression The expression whose sample-based standard deviation is calculated over a set of rows. The expression is commonly a column name.

DOUBLE

This function converts its argument to DOUBLE, and performs the computation in double-precision floating-point arithmetic.

The standard deviation (s) is computed according to the following formula, which assumes a normal distribution:

s = [ (1/( N - 1 )) * SUM( x_I - mean( x ) )² ]^½

This standard deviation does not include rows where numeric-expression is NULL. It returns NULL for a group containing either 0 or 1 rows.

For more information about the statistical computation performed, see Mathematical formulas for the aggregate functions.

Syntax 2 represents usage as a window function in a SELECT statement. As such, elements of window-spec can be specified either in the function syntax (inline), or in conjunction with a WINDOW clause in the SELECT statement. See the window-spec definition for the WINDOW clause.

For more information about using window functions in SELECT statements, including working examples, see Window functions.

For more information about specifying a window specification in an OVER clause, see Window definition: Inlining using the OVER clause and WINDOW clause.

SQL/2008 The STDDEV_SAMP function comprises part of optional SQL /2008 language feature T621, "Enhanced numeric functions". When used as window function, STDDEV_SAMP comprises part of optional SQL foundation feature T611, "Elementary OLAP operations".
The ability to specify DISTINCT over an expression that is not a column reference comprises part of optional SQL language feature F561, "Full value expressions". SQL Anywhere also supports SQL/2008 language feature F441, "Extended set function support", which permits operands of aggregate functions to be arbitrary expressions possibly including outer references to expressions in other query blocks that are not column references.

SQL Anywhere does not support optional SQL/2008 feature F442, "Mixed column references in set functions". SQL Anywhere does not permit the arguments of an aggregate function to include both a column reference from the query block containing the STDDEV_SAMP function, combined with an outer reference. For an example, see AVG function [Aggregate].

The following statement lists the average and variance in the number of items per order in different time periods:

SELECT YEAR( ShipDate ) AS Year,
    QUARTER( ShipDate ) AS Quarter,
    AVG( Quantity ) AS Average,
    STDDEV_SAMP( quantity ) AS Variance
FROM SalesOrderItems
GROUP BY Year, Quarter
ORDER BY Year, Quarter;

Year	Quarter	Average	Variance
2000	1	25.775148	14.3218...
2000	2	27.050847	15.0696...
...	...	...	...

STDDEV_SAMP function [Aggregate]

Syntax 1

Syntax 2

Parameters

Returns

Remarks

See also

Standards and compatibility

Example