SQL 2005 Median and Percentile

SQL Aggregate functions perform a calculation on a set of values and return a single value. Microsoft SQL Server supports only basic aggregate functions, such as SUM or MAX, whilst many of the statistical and analytical functions are missing. QUIP IT Solutions provides user made aggregate functions which enhance T-SQL scripting environment so you can calculate median and percentile within the databa like any other built-in aggregate function. It makes your SQL/TSQL queries and stored procedures much faster, more reliable and more efficient than any other technique. It also makes your script much simpler and cleaner. Configuring these functions is very straightforward. You are given a script file which you run like any other SQL script against the database you want to create these aggregate functions in. And that is it! you are now ready to use our aggregate functions the same way as you use any other SQL aggregate function with or without a "group by" statement.SQL Server 2005 does not support multiple parameters in user made aggregate functions. So, for each percentile calculation from 1 to 99 we have created a seperate function. For instance, for a table "tmp" with two fields "a" and "b" in order to calculate 6th, 50th, 90th ,95th percentile and median of field a by field b, following queries will get the required results. Select b,dbo.percentile06(a) from tmp group by b --6th Percentile Select b,dbo.percentile50(a) from tmp group by b --50th Percentile Select b,dbo.percentile90(a) from tmp group by b --90th Percentile Select b,dbo.percentile95(a) from tmp group by b --95th Percentile Select b,dbo.median(a) from tmp group by b -- Median