Oracle Variance, VAR_POP, VAR_SAMP
The query below shows the three Oracle variance functions - VARIANCE, variance based on population (VAR_POP) and variance based on sample (VAR_SAMP). The variance based on population is all data (6 rows represented by COUNT(*) in the query) and sample is 1 less than all data (6-1).

For i = 1 to 6
AVG (Average) = SUM(Xi)/n

VARIANCE = SUM((Xi - AVG)2)/(n-1)
VAR_SAMP = SUM((Xi - AVG)2)/(n-1)
VAR_POP = SUM((Xi - AVG)2)/n
Oracle Variance 
WITH q_tab AS
 (SELECT  1 idx, 10 val FROM DUAL UNION 
  SELECT  2 idx, 20 val FROM DUAL UNION 
  SELECT  3 idx, 30 val FROM DUAL UNION 
  SELECT  4 idx, 40 val FROM DUAL UNION 
  SELECT  5 idx, 40 val FROM DUAL UNION 
  SELECT  6 idx, 50 val FROM DUAL)
SELECT
  COUNT(*) pop_,
  SUM(val) sum_,
  ROUND(AVG(val),4) avg_,
  MEDIAN(val) median_,
  ROUND(VARIANCE(val),4) var_,
  ROUND(VAR_POP(val),4) var_pop_,
  ROUND(VAR_SAMP(val),4) var_samp_
FROM q_tab;

Oracle Variance Function Output

Variance Computation For Above Query Data 
Average (AVG) = (10+20+30+40+40+50)/6 = 190/6 = 31.6667

Variance (Pop) = ((10-AVG)2+(20-AVG)2+ ... +(50-AVG)2)/6 = 180.5556

Variance (Samp) = ((10-AVG)2+(20-AVG)2+ ... +(50-AVG)2)/5 = 216.6667

Oracle Standard Deviation

Oracle registered trademark of Oracle Corporation.
Last Revised on: March 01, 2012

  72813