Bulk Calculation of Quartile and Median Values in SQL Server

Previously, I posted about calculating median and quartiles in Analysis Services. In this post, I’ll do the same calculations over multiple sets of rows in a SQL Server database.

SQL Server offers an NTILE() function, but this is not equivalent to the Quartile() function in Excel which is the functionality we are trying to replicate. NTILE() is a window function that essentially returns a value representing the group into which each record would fall if you are trying to rank records. What we want is an aggregation function that will return a single value (25th percentile, 50th percentile or median, or 75th percentile) for each group of records.

The code below is an example of how this can be done in a single SQL statement.
Read more of this post

Advertisements