Tutorial Gateway

  • C
  • C#
  • Java
  • Python
  • SQL
  • MySQL
  • Js
  • BI Tools
    • Informatica
    • Talend
    • Tableau
    • Power BI
    • SSIS
    • SSRS
    • SSAS
    • MDX
    • R Tutorial
    • Alteryx
    • QlikView
  • More
    • C Programs
    • C++ Programs
    • Python Programs
    • Java Programs

SQL PERCENTILE_CONT

by suresh

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. The basic syntax of the PERCENTILE_CONT in SQL Server 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 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.

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

SQL PERCENTILE_CONT Example 2

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

Placed Under: SQL

  • C Tutorial
  • C# Tutorial
  • Java Tutorial
  • JavaScript Tutorial
  • Python Tutorial
  • MySQL Tutorial
  • SQL Server Tutorial
  • R Tutorial
  • Power BI Tutorial
  • Tableau Tutorial
  • SSIS Tutorial
  • SSRS Tutorial
  • Informatica Tutorial
  • Talend Tutorial
  • C Programs
  • C++ Programs
  • Java Programs
  • Python Programs
  • MDX Tutorial
  • SSAS Tutorial
  • QlikView Tutorial

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

Home | About Us | Contact Us | Privacy Policy