Friday, August 24, 2012

Calculate Median value in Oracle

There are three functions that calculate median values, median(), percentile_count() and percentile_disc.
1. The data. 
select num from  TBL_TEST order by num;
       NUM
----------
         1
         1
         2
         2
         2
         3
         4
         4
         5

2. Calculate the median or 50th percentile.
select median(num) f_median,
   PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY num)  f_percentile_cont,
   PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY num) f_percentile_disc
from TBL_TEST ;
  F_MEDIAN F_PERCENTILE_CONT F_PERCENTILE_DISC
---------- ----------------- -----------------
         2                 2                 2

No comments: