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

UUIDTOSTR function [String] Next Page

VAR_POP function [Aggregate]

Computes the statistical variance of a population consisting of a numeric-expression, as a DOUBLE.

Syntax 1

VAR_POP( numeric-expression )

Syntax 2

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

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


numeric-expression    The expression whose population-based variance 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 variance (s2) of numeric-expression (x) is computed according to the following formula:

s2 = (1/N) * SUM( xi - mean( x ) )2

This variance does not include rows where numeric-expression is NULL. It returns NULL for a group containing no rows.

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