Showing posts with label calculate median. Show all posts
Showing posts with label calculate median. Show all posts

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