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
SELECT SalesOrderID, OrderQty, ProductID,
OVER (PARTITION BY SalesOrderID) AS MedianCont
FROM Sales.SalesOrderDetail
WHERE SalesOrderID IN (43670, 43669, 43667, 43663)

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. Pingback: SQL SERVER – Weekly Series – Memory Lane – #004 « SQL Server Journey with SQL Authority

  5. 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