Let's learn Percentile_Count Function of SQL Server 2012 (Denali)

Posted by in Sql Server category on for Beginner level | Points: 250 | Views : 4816 red flag

In this article we will learn Percentile_Count Function of SQL Server 2012 (Denali)


Sql Server 2012 has brought a lot of new functions for the T-Sql developers. In this article we will look into Percentile_Count Function.


An inverse distribution function which takes a percentile value and a sort specification and returns an interpolated value that would fall into that percentile value with respect to the sort specification.It accepts any numeric data-type or any non-numeric data-type that can be implicitly converted to a numeric data-type as it's argument and returns the same data-type as the numeric data-type of the argument.


Percentile_Cont ( numeric_literal ) within group(Order by sort_expression [asc|desc]) OVER ([partition_by_clause])

A case study

Let us first create the environment

 IF EXISTS (SELECT * FROM sys.objects WHERE name = N'MatchTable' AND type = 'U')
    DROP TABLE MatchTable
--Create the table
CREATE TABLE [dbo].[MatchTable](
	[MatchID] [int] IDENTITY(1,1) NOT NULL,
	[MatchGroup] [varchar](8) NULL,
	[MatchBetween] [varchar](50) NULL,
	[ScheduleDate] [date] NULL

--Insert records
Insert Into MatchTable Values
('Group-A','India VS Australia','08/14/2011')
,('Group-A','India VS Pakistan','08/15/2011')
,('Group-A','India VS Newzealand','08/16/2011')
,('Group-A','Australia VS Pakistan','08/17/2011')
,('Group-A','Australia VS Newzealand','08/18/2011')
,('Group-A','Newzealand VS Pakistan','08/19/2011')
,('Group-B','USA VS WestIndies','08/20/2011')
,('Group-B','USA VS Ireland','08/21/2011')
,('Group-B','USA VS Bangaladesh','08/22/2011')
,('Group-B','WestIndies VS Ireland','08/23/2011')
,('Group-B','WestIndies VS Bangaladesh','08/24/2011')
,('Group-B','Ireland VS Bangaladesh','08/25/2011')

-- Project the records
Select * From MatchTable

Now let us run the below query

      ,Percentile_Cont =PERCENTILE_Cont(.5) WITHIN GROUP (ORDER BY MatchID)OVER(PARTITION BY [MatchGroup])		  
  FROM MatchTable

How it worked?

  1. For EVEN element count, find the two middle digits and divide by two.
  2. For ODD element count, find the middle digit.

So let us see, how it works. For "Match Group - A", the total record count is 6 which is Even. The middle elements are 3 and 4 and so as per the thumb rule the Percentile Count is (3/4)/2 = 3.5.


SQL Server 2012 (Denali) seems to be very mature and promising and has embedded with many new functions.In this article we have looked into the Percentile_Count function and it's internal working.Hope the article will be useful.

Page copy protected against web site content infringement by Copyscape

About the Author

Full Name: Niladri Biswas
Member Level: Platinum
Member Status: Member
Member Since: 10/25/2010 11:04:24 AM
Country: India
Best Regards, Niladri Biswas
Technical Lead at HCL Technologies

Login to vote for this post.

Comments or Responses

Login to post response

Comment using Facebook(Author doesn't get notification)