PERCENTILE_CONT (Transact-SQL)

Article
05/23/2023

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW) SQL Endpoint in Microsoft Fabric Warehouse in Microsoft Fabric

Calculates a percentile based on a continuous distribution of the column value in SQL Server. The result is interpolated and might not be equal to any of the specific values in the column.

Transact-SQL syntax conventions

Syntax

PERCENTILE_CONT ( numeric_literal )   
    WITHIN GROUP ( ORDER BY order_by_expression [ ASC | DESC ] )  
    OVER ( [ <partition_by_clause> ] )

Note

To view Transact-SQL syntax for SQL Server 2014 and earlier, see Previous versions documentation.

Arguments

numeric_literal
The percentile to compute. The value must range between 0.0 and 1.0.

WITHIN GROUP ( ORDER BY order_by_expression [ ASC | DESC ])
Specifies a list of numeric values to sort and compute the percentile over. Only one order_by_expression is allowed. The expression must evaluate to an exact or approximate numeric type, with no other data types allowed. Exact numeric types are int, bigint, smallint, tinyint, numeric, bit, decimal, smallmoney, and money. Approximate numeric types are float and real. The default sort order is ascending.

OVER ( <partition_by_clause> )
Divides the result set produced by the FROM clause into partitions to which the percentile function is applied. For more information, see OVER Clause (Transact-SQL). The <ORDER BY clause> and <rows or range clause> of the OVER syntax can't be specified in a PERCENTILE_CONT function.

Return Types

float(53)

Compatibility Support

Under compatibility level 110 and higher, WITHIN GROUP is a reserved keyword. For more information, see ALTER DATABASE Compatibility Level (Transact-SQL).

General Remarks

Any nulls in the data set are ignored.

PERCENTILE_CONT is nondeterministic. For more information, see Deterministic and Nondeterministic Functions.

Examples

A. Basic syntax example

The following example uses PERCENTILE_CONT and PERCENTILE_DISC to find the median employee salary in each department. These functions may not return the same value. PERCENTILE_CONT interpolates the appropriate value, which may or may not exist in the data set, while PERCENTILE_DISC always returns an actual value from the set.

USE AdventureWorks2012;  
  
SELECT DISTINCT Name AS DepartmentName  
      ,PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY ph.Rate)   
                            OVER (PARTITION BY Name) AS MedianCont  
      ,PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY ph.Rate)   
                            OVER (PARTITION BY Name) AS MedianDisc  
FROM HumanResources.Department AS d  
INNER JOIN HumanResources.EmployeeDepartmentHistory AS dh   
    ON dh.DepartmentID = d.DepartmentID  
INNER JOIN HumanResources.EmployeePayHistory AS ph  
    ON ph.BusinessEntityID = dh.BusinessEntityID  
WHERE dh.EndDate IS NULL;

Here is a partial result set.

DepartmentName        MedianCont    MedianDisc
--------------------   ----------   ----------
Document Control       16.8269      16.8269
Engineering            34.375       32.6923
Executive              54.32695     48.5577
Human Resources        17.427850    16.5865

B. Basic syntax example

-- Uses AdventureWorks  
  
SELECT DISTINCT DepartmentName  
,PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY BaseRate)  
    OVER (PARTITION BY DepartmentName) AS MedianCont  
,PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY BaseRate)  
    OVER (PARTITION BY DepartmentName) AS MedianDisc  
FROM dbo.DimEmployee;