0

Is there a code in sql that will allow me to get a median of column x within each percentile I pulled in one select statement? Here is the percentiles I pulled. I am trying to avoid coding the median individually and merge them together. Thanks!

PERCENTILE_CONT(0.01) WITHIN GROUP (ORDER BY PP01DS  ASC) as percentile_01,
PERCENTILE_CONT(0.05) WITHIN GROUP (ORDER BY PP01DS  ASC) as percentile_05,
PERCENTILE_CONT(0.10) WITHIN GROUP (ORDER BY PP01DS  ASC) as percentile_10,
PERCENTILE_CONT(0.20) WITHIN GROUP (ORDER BY PP01DS  ASC) as percentile_20,
PERCENTILE_CONT(0.30) WITHIN GROUP (ORDER BY PP01DS  ASC) as percentile_30,
PERCENTILE_CONT(0.40) WITHIN GROUP (ORDER BY PP01DS  ASC) as percentile_40,
PERCENTILE_CONT(0.50) WITHIN GROUP (ORDER BY PP01DS  ASC) as percentile_50,
PERCENTILE_CONT(0.60) WITHIN GROUP (ORDER BY PP01DS  ASC) as percentile_60,
PERCENTILE_CONT(0.70) WITHIN GROUP (ORDER BY PP01DS  ASC) as percentile_70,
PERCENTILE_CONT(0.80) WITHIN GROUP (ORDER BY PP01DS  ASC) as percentile_80,
PERCENTILE_CONT(0.90) WITHIN GROUP (ORDER BY PP01DS  ASC) as percentile_90,
PERCENTILE_CONT(0.99) WITHIN GROUP (ORDER BY PP01DS  ASC) as percentile_99,
Paul Maxwell
  • 33,002
  • 3
  • 32
  • 51
June Moon
  • 1
  • 1
    Please provide sample data and desired results. – Gordon Linoff Apr 12 '21 at 23:24
  • Medians are the "mid point" of any sample, so you need access to all the rows of each percentile group. Hence you do NOT want to pivot the data into 12 percentile columns then attempt to calculate medians for each of those. Use an intermediate result that is not pivoted (maybe in a CTE) then calculate medians, then pivot the result. – Paul Maxwell Apr 12 '21 at 23:33
  • Here is the sample data and expected results: PP01DS PLANPAY01DS 1 23.93533333 1.07142857 25.015 1.07142857 26.31 1.07142857 26.45928571 1.07142857 26.47321429 1.16666667 24.082 1.42857143 25.20607143 1.42857143 25.44928571 2 23.48033333 5.17266667 20.69 5.32714286 21.30785714 5.34285714 21.37142857 8.16142857 19.04357143 PERCENTILE_50 of PP01DS = 1.428571 Median PlanPay01DS = 25.32768 PERCENTILE_80 of PP01DS = 5.265352 Median PlanPay01DS = 25.11053 – June Moon Apr 13 '21 at 00:31
  • Here is the simpler example PP01DS PLANPAY01DS 1 10 2 20 3 30 5 50 PERCENTILE_50 of PP01DS = 2.5 Median PlanPay01DS = 15 – June Moon Apr 13 '21 at 00:42

0 Answers0