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

VAR_POP function [Aggregate] Next Page

VAR_SAMP function [Aggregate]


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

Syntax 1

VAR_SAMP( numeric-expression )

Syntax 2

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

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

Parameters

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

Remarks

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

The variance (s2) of numeric-expression (x) is computed according to the following formula, which assumes a normal distribution:

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

This variance does not include rows where numeric-expression is NULL. It returns NULL for a group containing either 0 or 1 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
Example

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_SAMP( quantity ) AS Variance
FROM SalesOrderItems
GROUP BY Year, Quarter
ORDER BY Year, Quarter;
YearQuarterAverageVariance
2000125.775148205.1158...
2000227.050847227.0939...
............