If you’re using SQL Server’s APPROX_PERCENTILE_DISC() function, and you’re getting error 10751 that reads “The ORDER BY in WITHIN GROUP clause of ‘APPROX_PERCENTILE_DISC’ function must have exactly one expression“, it’s probably because you’re passing too many ORDER BY expressions.

The APPROX_PERCENTILE_DISC() function accepts just one ORDER BY expression in its WITHIN GROUP clause.

To fix, be sure to use just one ORDER BY expression in the WITHIN GROUP clause when using the APPROX_PERCENTILE_DISC() function.

Example of Error

Here’s an example of code that produces the error:

SELECT
    APPROX_PERCENTILE_DISC( 0.5 ) 
        WITHIN GROUP ( ORDER BY ProductPrice, ProductId )
FROM Products;

Result:

Msg 10751, Level 15, State 2, Server 08197d4d4b6d, Line 32
The ORDER BY in WITHIN GROUP clause of 'APPROX_PERCENTILE_DISC' function must have exactly one expression.

I got the error because I provided two expressions to the ORDER BY clause. The function only accepts one ORDER BY expression, and so it returned an error.

Here’s what happens when we provide zero ORDER BY expressions:

SELECT
    APPROX_PERCENTILE_DISC( 0.5 ) 
        WITHIN GROUP ( ORDER BY )
FROM Products;

Result:

Msg 102, Level 15, State 1, Server 89aa61a72822, Line 33
Incorrect syntax near ')'.

This is now syntax error 102, which is a different error message altogether. Either way, the reason I got the error is because I didn’t pass any expressions to the ORDER BY clause.

I get the same syntax error if I remove the ORDER BY clause altogether (but leave the parentheses):

SELECT
    APPROX_PERCENTILE_DISC( 0.5 ) 
        WITHIN GROUP ( )
FROM Products;

Result:

Msg 102, Level 15, State 1, Server 336b4aa98a1d, Line 33
Incorrect syntax near ')'.

But if I remove the parentheses, I get a different error:

SELECT
    APPROX_PERCENTILE_DISC( 0.5 ) 
        WITHIN GROUP
FROM Products;

Result:

Msg 156, Level 15, State 1, Server 16bdc9963f5d, Line 34
Incorrect syntax near the keyword 'FROM'.

This time it’s error 156.

Solution

Regardless of the exact error, all we need to do to fix the issue is to make sure our WITHIN GROUP clause has an ORDER BY clause with exactly one ORDER BY expression.

Example:

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

Result:

14.7500

This time it worked without error.

We also need to make sure that the ORDER BY expression uses a supported numeric type. Supported types are int, bigint, smallint, tinyint, bit, smallmoney, money, float and real. Decimal and float data types are not supported.

Passing an ORDER BY expression of the wrong type will result in a different error.