Q:

Retrieve the Median from a decimal column using PERCENTILE_CONT SQL

I have a table Prices Like:
ID                   PurchasePriceCalc
0146301              0.002875161
00006L00             0.00396
00087G03             NULL
00001G04             0.0020004
00006S               0.003689818
01580h01             NULL
00082EE00            0.002462687
00038R05             0.002237565
01666R01             0.002666667

I Would like to get the Median per each PurchasePriceCalc and then subtract the result with the PurchasePriceCalc, for a better explanation the Formula should be : (PurchasePriceCalc - Median(PurchasePriceCalc)).
I'm using the query below but is not working:
SELECT ID,PurchasePriceCalc, PurchasePriceCalc - PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY PurchasePriceCalc)
                         OVER (PARTITION BY ID) AS MediaCalc   FROM Prices

This is how should be the Output, (Yellow Column).

Any assistance or help would be really appreciated!

A:

I guess the problem is with OVER (PARTITION BY ID). If ID is UNIQUE then each group consists of only one row that is why you get all values equal 0/NULL.
You should remove PARTITION BY part.
SELECT ID,PurchasePriceCalc, 
 PurchasePriceCalc - PERCENTILE_CONT(0.5)
                   WITHIN GROUP(ORDER BY PurchasePriceCalc) OVER () AS MediaCalc 
FROM Prices;

