CODEWITHSUNDEEP
sqlsql-servert-sqlpercentile
JC_BI
JC_BI

Reputation: 419

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). enter image description here

Any assistance or help would be really appreciated!

Upvotes: 1

Views: 217

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 175586

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;

Upvotes: 2

Related Questions