CODEWITHSUNDEEP
sqlsql-server
Rich
Rich

Reputation: 6561

PERCENTILE_CONT for median and rounding value to 4 decimal places

I'm able to select median values using PERCENTILE_CONT, but I also want the values to only contain 4 decimal places.

When I do this I get an error saying:

'PERCENTILE_CONT' must have an OVER clause.

This is my code:

SELECT 
    FIELD1,
    CAST(PERCENTILE_CONT(0.5) AS DECIMAL(10,4)) WITHIN GROUP (ORDER BY FIELD1) OVER (PARTITION BY FIELD1) AS Median
FROM
    TABLE1

What am I doing wrong?

Upvotes: 1

Views: 1301

Answers (1)

Alex
Alex

Reputation: 5157

As the error implies PERCENTILE_CONT is not a stand alone function. You need to cast the whole block of keywords

CAST(PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY FIELD1) OVER (PARTITION BY FIELD1) AS DECIMAL(10,4)) AS Median

Upvotes: 3

Related Questions