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

The APPROX_PERCENTILE_CONT() function requires the WITHIN GROUP clause, and that clause requires an ORDER BY sub-clause. However, that ORDER BY sub-clause requires exactly one expression – no more, no less. So, you can’t pass multiple expressions, and you can’t pass zero expressions. It must be exactly one expression.

To fix this issue, be sure to have just one ORDER BY expression in the WITHIN GROUP clause when using the APPROX_PERCENTILE_CONT() function.

Example of Error

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

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

Result:

Msg 10751, Level 15, State 2, Server 75a3d9bc3d8d, Line 31
The ORDER BY in WITHIN GROUP clause of 'APPROX_PERCENTILE_CONT' function must have exactly one expression.

I got the error because I provided two expressions to the ORDER BY clause.

If I provide none, I get a syntax error:

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

Result:

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

So this is actually a different error message altogether (it’s error msg 102). 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 error if I remove the ORDER BY clause altogether (but leave the parentheses):

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

Result:

Msg 102, Level 15, State 1, Server a9a6150bd4e5, Line 32
Incorrect syntax near ')'.

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

SELECT
    APPROX_PERCENTILE_CONT( 0.5 ) 
        WITHIN GROUP
FROM Products;

Result:

Msg 156, Level 15, State 1, Server a9a6150bd4e5, Line 33
Incorrect syntax near the keyword 'FROM'.

Still a syntax error, but this time it’s error msg 156.

Solution

Regardless of the exact error message, 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_CONT( 0.5 ) 
        WITHIN GROUP ( ORDER BY ProductPrice )
FROM Products;

Result:

20.369999999999997

This time it worked without error.