SQL Server PERCENTILE_CONT() Function

In SQL Server, the PERCENTILE_CONT() function is a statistical function used to calculate the value corresponding to a specified percentile. It can be used to calculate the median, quartiles, and mode of continuous variables, as well as the mode of non-continuous variables.

Syntax

The syntax for the PERCENTILE_CONT() function is as follows:

PERCENTILE_CONT ( percentile ) WITHIN GROUP (ORDER BY expression) OVER ( [partition_by_clause] )

Here, the percentile parameter represents the percentile to be calculated, which ranges from 0 to 1. The expression parameter represents the value to be calculated, and the partition_by_clause represents the optional partition clause used to partition the dataset.

Usage

The PERCENTILE_CONT() function is commonly used in the following scenarios:

To calculate the median, quartiles, and other statistics of continuous variables.
To calculate the mode and other statistics of non-continuous variables.
In scenarios such as data analysis and report creation, to perform statistical analysis on datasets.

Examples

The following are two examples of using the PERCENTILE_CONT() function.

Example 1

Assume that there is an employee table containing information such as employee ID, name, and salary. We want to calculate the median salary of this table. We can use the following SQL statement:

SELECT
  PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary) OVER () AS median_salary
FROM
  employees

After executing the above SQL statement, we will get the following result:

median_salary
5000

Example 2

Assume that there is a sales table containing information such as sales order number, sales date, and sales amount. We want to calculate the 75th percentile of sales amount in this table. We can use the following SQL statement:

SELECT
  PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY sales_amount) OVER () AS p75_sales_amount
FROM
  sales