ASA SQL Reference
SQL Functions
Alphabetical list of functions
Computes the statistical variance of a sample consisting of a numeric-expression, as a DOUBLE.
VAR_SAMP ( numeric-expression )
numeric-expression The expression whose sample-based variance is calculated over a set of rows. The expression is commonly a column name.
The variance (s2) 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 no rows.
SQL/92 Vendor extension.
SQL/99 SQL/foundation feature outside of core SQL. The VARIANCE syntax is a vendor extension.
Sybase Not supported by Adaptive Server Enterprise.
The following statement lists the average and variance in the number of items per order in different time periods:
SELECT year( ship_date ) AS Year, quarter( ship_date ) AS Quarter, avg( quantity ) AS Average, var_samp( quantity ) AS Variance FROM sales_order_items GROUP BY Year, Quarter ORDER BY Year, Quarter
Year | Quarter | Average | Variance |
---|---|---|---|
2000 | 1 | 25.775148 | 205.1158... |
2000 | 2 | 27.050847 | 227.0939... |
... | ... | ... | ... |