When using the APPROX_PERCENTILE_DISC() function in SQL Server, you may get an error that reads “The function ‘APPROX_PERCENTILE_DISC’ must have a WITHIN GROUP clause“. If you get this error, it’s because you omitted the WITHIN GROUP clause when using the APPROX_PERCENTILE_DISC() function.

To fix this issue, make sure you include the WITHIN GROUP clause whenever you use the APPROX_PERCENTILE_DISC() function.

Example of Error

Here’s an example of code that results in the error:

SELECT APPROX_PERCENTILE_DISC( 0.8 )
FROM Products;

Result:

Msg 10754, Level 15, State 2, Server dc69fbeceae9, Line 31
The function 'APPROX_PERCENTILE_DISC' must have a WITHIN GROUP clause.

This error occurred because I omitted the WITHIN GROUP clause.

Solution

To fix this problem, simply include the WITHIN GROUP clause when using the APPROX_PERCENTILE_DISC() function:

SELECT
    APPROX_PERCENTILE_DISC( 0.8 ) 
        WITHIN GROUP ( ORDER BY ProductPrice )
FROM Products;

Result:

33.4900

As expected, this resolved the issue.

I should also mention that the WITHIN GROUP clause requires an ORDER BY clause with exactly one ORDER BY expression. If we provide more ORDER BY expressions (or less), we’ll get a different error.