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 () AS MediaCalc FROM Prices
This is how should be the Output, (Yellow Column):
Any assistance or help would be really appreciated!