SQL PERCENTILE_DISC

The SQL PERCENTILE_DISC will calculate a percentile of the sorted values within an entire row set, or within the partitions in a table. The basic syntax of the PERCENTILE_DISC in SQL Server analytic function is as shown below:

SELECT PERCENTILE_DISC(Numerical_Literal)
       WITHIN GROUP ( ORDER BY_Clause)
        OVER ( PARTITION_BY_Clause )
FROM [Source]
  • Numerical_Literal: Specify the Percentile to compute. This value should be between 0.0 and 1.0
  • WITHIN Group (Order By_Clause): This will sort the column data in a specified order within the group. Visit Order By Clause for a better knowing.
  • Over (Partition_By_Clause): It separates the records selected by the SELECT Statement into SQL Server partitions.

We are going to utilize the below-shown data for this percentile disc demonstration

SQL PERCENTILE_DISC 1

SQL PERCENTILE_DISC Example

This example shows 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_DISC(0.0) WITHIN GROUP (ORDER BY [Sales] ASC)
			   OVER (PARTITION BY [Occupation]) AS [Percentile Disc] 
  FROM [Employee]
SQL PERCENTILE_DISC 2

Let me change the Order by from Ascending order to Descending.

SELECT [FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
      ,PERCENTILE_DISC(0.0) WITHIN GROUP (ORDER BY [Sales] DESC)
			   OVER (PARTITION BY [Occupation]) AS [Percentile Disc] 
  FROM [Employee]
SQL PERCENTILE_DISC 3

PERCENTILE_DISC Example 2

In this case, we will use different numerical values inside the SQL PERCENTILE_DISC function.

SELECT [FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
      ,PERCENTILE_DISC(0.25) WITHIN GROUP (ORDER BY [Sales] ASC)
		OVER (PARTITION BY [Occupation]) AS [Percentile Disc 1]  
      ,PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY [Sales] ASC)
		OVER (PARTITION BY [Occupation]) AS [Percentile Disc 2]
      ,PERCENTILE_DISC(0.75) WITHIN GROUP (ORDER BY [Sales] ASC)
		OVER (PARTITION BY [Occupation]) AS [Percentile Disc 3] 
      ,PERCENTILE_DISC(1.0) WITHIN GROUP (ORDER BY [Sales] ASC)
		OVER (PARTITION BY [Occupation]) AS [Percentile Disc 4] 
  FROM [Employee]
SQL PERCENTILE_DISC 4

About Suresh

Suresh is the founder of TutorialGateway and a freelance software developer. He specialized in Designing and Developing Windows and Web applications. The experience he gained in Programming and BI integration, and reporting tools translates into this blog. You can find him on Facebook or Twitter.