SQL SERVER – Introduction to PERCENTILE_CONT() – Analytic Functions Introduced in SQL Server 2012

SQL Server 2012 introduces new analytical function PERCENTILE_CONT().

The book online gives following definition of this function: Calculates a percentile based on a continuous distribution of the column value in Microsoft SQL Server 2012 Release Candidate 0 (RC 0). The result is interpolated and might not be equal to any of the specific values in the column.

If you are clear with understanding of the function – no need to read further. If you got lost here is the same in simple words – it is lot like finding median with percentile value.

Now let’s have fun following query:

USE AdventureWorks
GO
SELECT SalesOrderID, OrderQty, ProductID,
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY ProductID)
OVER (PARTITION BY SalesOrderID) AS MedianCont
FROM Sales.SalesOrderDetail
WHERE SalesOrderID IN (43670, 43669, 43667, 43663)
ORDER BY SalesOrderID DESC
GO

The above query will give us the following result:

You can see that I have used PERCENTILE_COUNT(0.5) in query, which is similar to finding median. Let me explain above diagram with little more explanation. The defination of median is as following:

In case of Even Number of elements = In ordered list add the two digits from the middle and devide by 2
In case of Odd Numbers of elements = In ordered list select the digits from the middle

I hope this example gives clear idea how PERCENTILE_CONT() works.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

About these ads

7 thoughts on “SQL SERVER – Introduction to PERCENTILE_CONT() – Analytic Functions Introduced in SQL Server 2012

  1. Pingback: SQL SERVER – Puzzle to Win Print Book – Explain Value of PERCENTILE_CONT() Using Simple Example « Journey to SQLAuthority

  2. Pingback: SQL SERVER – 2012 – Summary of All the Analytic Functions – MSDN and SQLAuthority « Journey to SQLAuthority

  3. i have one table , example table_student that have 2 file is student_ID and Student_Name . what can i loop file name of table_student , even thought i know table name is table_Student.
    anyone help me . thank

  4. As they say, there’s more than one way to skin a cat so it’s a shame that Microsoft have only offered one method of calculating the percentile. I’ve created my own percentile method which can be applied to Microsoft SQL Server 2005, 2008, 2008R2 and 2012. It offers four different methods to calculate percentile. Check it out here: http://sqladmintools.com/statistics.aspx

  5. Pingback: SQL SERVER – Weekly Series – Memory Lane – #004 « SQL Server Journey with SQL Authority

  6. As usual, you make things sound so much simpler and easier to understand.

    I was looking on Books Online and going “huh?” and the moment I read your post, it just clicks instantly.

    Thank you.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s