Solved

# Creating Percentile Aggregate Function In SQL Server 2005 Express

Posted on 2009-07-14

I am looking to create summary information on numerical data sets within SQL Server 2005 Express. These need to include additional aggregate statistics such as Percentiles (flexible percentiles preferentially i.e add the nth value of the percentile statistic as an argument). I am not sure as to the best approach but have been considering creating an Aggregate Function.

I am surprised that looking into doing this i have not found very much information on the web.

I am considering using a standard method such as that used in Excel to produce a percentile which would require ordering the aggregate group subset and determining the relevant rank of the percentile argument (simplistically 50th Percentile: 50/100 * n = rank) .

One concern i have is that i believe aggregate functions may split and merge subsets which would not work for ranking functions?

If anyone has experience of implementing statistical functions in this way or alternative methods i would be grateful in hearing about it.

Thanks