Tutorial Gateway

  • C Programming
  • Java
  • R
  • SQL
  • MySQL
  • Python
  • BI Tools
    • Informatica
    • Tableau
    • Power BI
    • SSIS
    • SSRS
    • SSAS
    • MDX
    • QlikView
  • About
    • About Us
    • Contact Us

SQL PERCENTILE_DISC

02-12-2017 by suresh Leave a Comment

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:

1
2
3
4
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.

1
2
3
4
5
6
7
8
9
10
11
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.

1
2
3
4
5
6
7
8
9
10
11
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.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
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

Share your Feedback, or Code!! Cancel reply

Trending

  • SQL Server Integration Services (SSIS)
  • SQL Server Tutorial
  • SQL Server Reporting Services (SSRS)
  • Home
  • C Program to Calculate the Sum and Average of n Number
  • Tableau
  • C Program to Print Prime Numbers from 1 to 100

Stay in Touch!

Sign Up to receive Email updates on Latest Tutorials

  • Blogger
  • C Programs
  • Java Programs
  • SQL
  • SSIS
  • SSRS
  • Tableau
  • JavaScript

Copyright © 2018 | Tutorial Gateway· All Rights Reserved by Suresh

Home | About Us | Contact Us | Privacy Policy