If you’re getting SQL Server error 402 that reads something like “The data types numeric and numeric are incompatible in the approx_percentile_disc operator“, it’s probably because you’re trying to use the APPROX_PERCENTILE_DISC() function on a column of the wrong data type.

It could be that you’ve simply passed the wrong column, or it could be that the column is the correct one, but it’s of the wrong type.

To fix, be sure that the column/expression is of a supported type.

Example of Error

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

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

Result:

Msg 402, Level 16, State 1, Server c2cfd5de5ad7, Line 30
The data types numeric and numeric are incompatible in the approx_percentile_disc operator.

In this case, my ProductPrice column was a numeric type, but that type isn’t supported. Microsoft’s documentation doesn’t list it as a supported type either. Specifically, it says this:

Only numeric data types are allowed. The expression must evaluate to a supported exact or approximate numeric type, with no other data types allowed. Supported exact numeric types are int, bigint, smallint, tinyint, bit, smallmoney, and money. Supported approximate numeric types are float and real. Decimal and float data types are not supported.

So the documentation explicitly states that “Decimal and float data types are not supported“.

In my case the column is a numeric type, which is a synonym for decimal. Therefore, it’s not supported.

Solution

The solution to the above error is to make sure that the expression is of a supported type. As mentioned, supported types are int, bigint, smallint, tinyint, bit, smallmoney, money, float and real.

So in my case, I could convert my numeric expression to money or smallmoney, given it contains price data:

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

Result:

14.7500

This time it ran without error.