SQL PERCENTILE_CONT 3

SQL PERCENTILE_CONT

The SQL Server PERCENTILE_CONT is one of the Analytic functions, which will calculate a percentile based on the continuous distribution of column values in a table. The basic syntax of the PERCENTILE_CONT 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 SQL PERCENTILE_CONT demonstration.

SQL PERCENTILE_CONT 1

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.

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 2

SQL PERCENTILE_CONT Example 2

Here, we use different numerical values inside the PERCENTILE_CONT function.

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]
SQL PERCENTILE_CONT 3