question

JCACERES avatar image
JCACERES asked

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 () AS MediaCalc 
FROM Prices

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

Any assistance or help would be really appreciated!

sqltsqlsqlserverstatistics
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

0 Answers

·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.