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

Posted by in Sql Server category on for Beginner level | Points: 250 | Views : 4185

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

## Introduction

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.

## Purpose

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.

## Syntax

`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
GO
SET ANSI_NULLS ON
GO
--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
) ON [PRIMARY]
GO

--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','WestIndies VS Ireland','08/23/2011')

-- Project the records
Select * From MatchTable
```

Now let us run the below query

```SELECT [MatchID]
,[MatchGroup]
,[MatchBetween]
,[ScheduleDate]
,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.

## Conclusion

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.