Oracle STDDEV, STDDEV_POP, STDDEV_SAMP
The query below shows the three SQL Standard Deviation (STDDEV) functions in Oracle. The standard deviation (STDDEV) and standard deviation sample (STDDEV_SAMP) are based population-1 (6-1) computation (population is COUNT(*)) and standard deviation population (STDDEV_POP) is for the entire population (6 in this query).

For i = 1 to n (n = 6 in the query shown below)
AVG (Average) = SUM(Xi)/n

SD (sample) = SQRT(SUM((Xi - Xmean)2/(n-1))
SD (population) = SQRT(SUM((Xi - Xmean)2/n)

The MEDIAN function computes the average of the two mid values, idx 3 and 4, which are 30 and 40 respectively ((30 + 40)/2 = 35) in the query shown (beside to the right).
Oracle STDDEV 
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(STDDEV(val),4) sd_,
  ROUND(STDDEV_POP(val),4) sd_pop_,
  ROUND(STDDEV_SAMP(val),4) sd_samp_
FROM q_tab;

Oracle Standard Deviation Function Output

Standard Deviation Computation 
Average (AVG) = 190/6 = 31.6667

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

SD (Pop) = SQRT(180.5556) = 13.4371

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

SD (Samp) = SQRT(216.6667) = 14.7196

Oracle Variance

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

  73947