Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 10.0.1 » SQL Anywhere Server - SQL Reference » SQL Functions » Alphabetical list of functions

STDDEV function [Aggregate] Next Page

STDDEV_POP function [Aggregate]

Computes the standard deviation of a population consisting of a numeric-expression, as a DOUBLE.

Syntax 1

STDDEV_POP( numeric-expression )

Syntax 2

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

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


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


This function converts its argument to DOUBLE, performs the computation in double-precision floating point, and returns a DOUBLE as the result.

The population-based standard deviation (s) is computed according to the following formula:

s = [ (1/N) * SUM( xi - mean( x ) )2 ]1/2

This standard deviation does not include rows where numeric-expression is NULL. It returns NULL for a group containing no 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 provided in WINDOW clause.

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

See also
Standards and compatibility

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_POP( quantity ) AS Variance
FROM SalesOrderItems
GROUP BY Year, Quarter
ORDER BY Year, Quarter;