Quick Search:
 
 Oracle PL/SQL: VARIANCE Jump to:  
Category: >> Oracle PL/SQL >> VARIANCE  

<< lastnext >>

Snippet Name: VARIANCE

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.

Also see:
» RANK
» REGR_SLOPE
» VAR_SAMP
» VAR_POP
» SUM
» STDDEV_SAMP
» STDDEV_POP
» STDDEV
» ROW_NUMBER
» REGR_SYY
» REGR_SXY
» REGR_SXX
» REGR_R2
» REGR_INTERCEPT
» REGR_COUNT
» REGR_AVGY
» REGR_AVGX
» Number Functions: RATIO_TO_REPORT
» Number Functions: RANK
» PERCENTILE_DISC
» PERCENTILE_CONT
» PERCENT_RANK
» OVER PARTITION BY
» NTILE
» MIN
» MAX
» LEAD
» LAST_VALUE
» LAST
» LAG

Comment: (none)

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:
 
SELECT VARIANCE(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 (ORDER BY hire_date) "Variance"
   FROM employees 
   WHERE department_id = 30; 
 
 
LAST_NAME           SALARY   VARIANCE
--------------- ---------- ----------
Raphaely             11000          0
Khoo                  3100   31205000
Tobias                2800 21623333.3
Baida                 2900 16283333.3
Himuro                2600   13317000
Colmenares            2500   11307000
 


 
   Home |    Search |    Code Library |    Sponsors |    Privacy |    Terms of Use |    Contact Us © 2003 - 2024 psoug.org