If you’re getting SQL Server error msg 402 that tells you the “data types” are “incompatible in the approx_percentile_cont operator“, it’s probably because you’re trying to apply the APPROX_PERCENTILE_CONT() function against a non-numeric column (or one that doesn’t evaluate to a numeric type).

When using the APPROX_PERCENTILE_CONT() function, the ORDER BY expression in the WITHIN GROUP clause must evaluate to an exact or approximate numeric type. Other data types are not allowed, and will result in the above error.

To fix this issue, be sure to apply the function against a numeric column/expression.

Example of Error

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

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

Result:

Msg 402, Level 16, State 1, Server 336c649056ca, Line 30
The data types numeric and varchar are incompatible in the approx_percentile_cont operator.

I got the error because I used a VARCHAR column as the ORDER BY expression. This is not a numeric type, and so I got the error.

Solution

To fix the issue, be sure to pass an expression that evaluates to an exact or approximate numeric type.

So to fix my issue, I can simply change the column passed to the ORDER BY clause:

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

Result:

20.369999999999997

Here I swapped ProductName for ProductPrice and it ran without error.

In my case the ProductPrice column is defined as DECIMAL(8,2), which is an exact numeric type. As mentioned, the ORDER BY clause requires an expression that evaluates to an exact or approximate numeric type, and my ProductPrice column satisfies that requirement.

Exact numeric types are int, bigint, smallint, tinyint, numeric, bit, decimal, smallmoney, and money. Approximate numeric types are float and real. No other data types are allowed, and will result in the above error.