Description: VARIANCE returns the variance of expr. You can use it as an aggregate or analytic function.
Oracle Database calculates the variance of expr as follows:
0 if the number of rows in expr = 1
VAR_SAMP if the number of rows in expr > 1
If you specify DISTINCT, then you can specify only the query_partition_clause of the analytic_clause. The order_by_clause and windowing_clause are not allowed.
This function takes as an argument any numeric datatype or any nonnumeric datatype that can be implicitly converted to a numeric datatype. The function returns the same datatype as the numeric datatype of the argument.
Language: PL/SQL Highlight Mode: PLSQL Last Modified: March 02nd, 2009
VARIANCE([DISTINCT | ALL]<value>) OVER (<analytic_clause>)--Aggregate Example--The following example calculates the variance of -- all salaries in the sample employees table:SELECTVARIANCE(salary)"Variance"FROM employees;VARIANCE----------15283140.5--Analytic Example--The following example returns the cumulative variance of -- salary values in Department 30 ordered by hire date.SELECT last_name, salary,VARIANCE(salary)
OVER (ORDERBY hire_date)"Variance"FROM employees
WHERE department_id =30;
LAST_NAME SALARY VARIANCE--------------- ---------- ----------
SQL University.net courses meet the most demanding needs of the business world for advanced education
in a cost-effective manner. SQL University.net courses are available immediately for IT professionals
and can be taken without disruption of your workplace schedule or processes.
Compared to traditional travel-based training, SQL University.net saves time and valuable corporate
resources, allowing companies to do more with less. That's our mission, and that's what we deliver.