Tutorial Gateway

SQL PERCENTILE_DISC

by

The SQL PERCENTILE_DISC will calculate a percentile of the sorted values within an entire row set, or within the partitions in a table.

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

SQL PERCENTILE_DISC Syntax

The basic syntax of the PERCENTILE_DISC in SQL Server 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. Please refer 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_DISC 1

SQL PERCENTILE_DISC Example

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

OUTPUT

SQL PERCENTILE_DISC 2

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

USE [SQL Tutorial]
GO
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]

OUTPUT

SQL PERCENTILE_DISC 3

SQL PERCENTILE_DISC Example 2

In this example we will use different numerical values inside the PERCENTILE_DISC function.

USE [SQL Tutorial]
GO
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]

OUTPUT

SQL PERCENTILE_DISC 4

Thank You for Visiting Our Blog

Placed Under: SQL