SQL PERCENTILE_CONT

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.

This article will show you, How to use this analytic function called PERCENTILE_CONT in SQL Server with example. Before we get into the example, let us see the syntax behind this:

SQL PERCENTILE_CONT Function Syntax

The basic syntax of the PERCENTILE_CONT in SQL Server is as shown below:

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 SQL Order By Clause for better understanding.
• Over (Partition_By_Clause): It divide the records selected by the SELECT Statement into partitions.

We are going to use the below shown data for this demonstration

SQL PERCENTILE_CONT Example

In this example we will 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]

OUTPUT

SQL PERCENTILE_CONT Example 2

In this example we will 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]

OUTPUT

Thank You for Visiting Our Blog