In SQL Server, the APPROX_PERCENTILE_CONT() function calculates and returns an approximate percentile based on a continuous distribution of the column value. This is an interpolated value from the set of values in a group based on percentile value and sort specification.

The APPROX_PERCENTILE_CONT() function can be used in place of the PERCENTILE_CONT() function if you just want to quickly get an approximate value. This could be useful on larger data sets, where using PERCENTILE_CONT() to get an accurate percentile value could take a much longer time to execute.

The APPROX_PERCENTILE_CONT() function was introduced in SQL Server 2022.

Syntax

The syntax goes like this:

APPROX_PERCENTILE_CONT (numeric_literal)
WITHIN GROUP (ORDER BY order_by_expression [ASC|DESC])

So numeric_literal is in the range between 0.0 and 1.0. This (required) value is the percentile to compute.

We must provide a WITHIN GROUP clause with an ORDER BY clause. Only one ORDER BY expression is allowed.

Example

Suppose we have the following table:

SELECT * FROM Products;

Result:

ProductId   VendorId    ProductName                              ProductPrice
----------- ----------- ---------------------------------------- ------------
          1        1001 Left handed screwdriver                         25.99
          2        1001 Right handed screwdriver                        25.99
          3        1001 Long Weight (blue)                              14.75
          4        1001 Long Weight (green)                             11.99
          5        1002 Sledge Hammer                                   33.49
          6        1003 Chainsaw                                       245.00
          7        1003 Straw Dog Box                                   55.99
          8        1003 Hammock                                         10.00
          9        1004 Bottomless Coffee Mugs (4 Pack)                  9.99
         10        1004 Tea Pot                                         12.45

We can use the APPROX_PERCENTILE_CONT() function against that table like this:

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

Result:

APPROX_PERCENTILE_CONT  
------------------------
      20.369999999999997

In this example, we used an argument of 0.5 in order to calculate the median price of all products in the table, sorted by product price (thanks to the WITHIN GROUP clause).

The APPROX_PERCENTILE_CONT() function doesn’t necessarily return a value from the actual data set. We can see that in our result (our result doesn’t appear anywhere in the underlying table). This is because the APPROX_PERCENTILE_CONT() function returns an interpolated value. It’s the same with the PERCENTILE_CONT() function – that function also returns an interpolated value.

SQL Server also has an APPROX_PERCENTILE_DISC() function (and a PERCENTILE_DISC() function) that works in a similar way to APPROX_PERCENTILE_CONT(), except that it always returns an actual value from the data set.

Another Example

Here’s what happens when we pass different percentile arguments to the function:

SELECT
    APPROX_PERCENTILE_CONT( 0.3 ) 
        WITHIN GROUP ( ORDER BY ProductPrice ) AS Result1,
    APPROX_PERCENTILE_CONT( 0.7 ) 
        WITHIN GROUP ( ORDER BY ProductPrice ) AS Result2,
    APPROX_PERCENTILE_CONT( 0.9 ) 
        WITHIN GROUP ( ORDER BY ProductPrice ) AS Result3
FROM Products;

Result:

Result1                  Result2                  Result3                 
------------------------ ------------------------ ------------------------
      12.311999999999999       28.240000000000002       74.890999999999934

The WITHIN GROUP Clause is Required

We must provide the WITHIN GROUP clause when using APPROX_PERCENTILE_CONT(). Here’s what happens if we remove it:

SELECT
    APPROX_PERCENTILE_CONT( 0.5 ) AS APPROX_PERCENTILE_CONT
FROM Products;

Result:

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

About the ORDER BY Clause

The WITHIN GROUP clause must have an ORDER BY sub-clause, and that ORDER BY clause must have exactly one expression (no more, no less).

Here’s what happens when we include more than one ORDER BY expression:

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.

The error message is self explanatory – we can only have one expression in the ORDER BY clause in the WITHIN GROUP clause.

Here’s what happens if we remove the ORDER BY clause altogether:

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

Result:

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

It tells us we have a syntax error. This is because the WITHIN GROUP clause doesn’t contain an ORDER BY sub-clause.

Documentation

For more information and examples, see Microsoft’s documentation for the APPROX_PERCENTILE_CONT() function.