The SQL PERCENTILE_CONT is one of the Analytic Function, which will calculate a percentile based on the continuous distribution of column values in a table. The basic syntax of the PERCENTILE_CONT in SQL Server is
SELECT PERCENTILE_CONT(Numerical_Literal)
WITHIN GROUP ( ORDER BY_Clause)
OVER ( PARTITION_BY_Clause )
FROM [Source]
- Numerical_Literal: The Percentile to compute. This value should be between 0.0 and 1.0
- WITHIN Group (Order By_Clause): This clause will sort the data in a specified order within the group. Please refer to SQL Order By Clause for a better understanding.
- Over (Partition_By_Clause): It divides the SQL Server records selected by the SELECT Statement into partitions.
We are going to use the below-shown data for this PERCENTILE_CONT demonstration
SQL PERCENTILE_CONT Example
In this example, we show you how to calculate the percentile of the partitioned records present in a table. The following Query will Order the Data by Sales in Ascending Order, partition the data by Occupation. And then calculate the percentile for each partition independently.
USE [SQL Tutorial]
GO
SELECT [FirstName]
,[LastName]
,[Education]
,[Occupation]
,[YearlyIncome]
,[Sales]
,PERCENTILE_CONT(0) WITHIN GROUP (ORDER BY [Sales] ASC)
OVER (PARTITION BY [Occupation]) AS Percentile
FROM [Employee]
SQL PERCENTILE_CONT Example 2
Here, we use different numerical values inside the PERCENTILE_CONT function.
USE [SQL Tutorial]
GO
SELECT [FirstName]
,[LastName]
,[Education]
,[Occupation]
,[YearlyIncome]
,[Sales]
,PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY [Sales] ASC)
OVER (PARTITION BY [Occupation]) AS Percentile1
,PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY [Sales] ASC)
OVER (PARTITION BY [Occupation]) AS Percentile2
,PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY [Sales] ASC)
OVER (PARTITION BY [Occupation]) AS Percentile3
,PERCENTILE_CONT(1.0) WITHIN GROUP (ORDER BY [Sales] ASC)
OVER (PARTITION BY [Occupation]) AS Percentile4
FROM [Employee]
