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; ``` 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

1321