October 4, 2006 at 6:52 am
I'm trying to calculate the median for a group. I have to assume this is possible. I've been following this thread.
I have used the code their to add all the numbers in my group to an array. Then at the footer of the group I call a function that calculates the median based on that array, wipes the array for the next group, and then returns the median.
It more or less works, except my results are a group behind. i.e. the median for the 1st group is in the 2nd group, the median for the 2nd group is in the 3rd group etc.
I believe it comes to aggregating these group numbers. You would run into the same problem if you were writing your own Sum() function.
Can anyone advise me on this?
October 5, 2006 at 5:20 am
Itzik Ben-Gan has an article in sqlmag.com on this topic. He describes both a SQL Server 2000 a simpler 2005 solution.
You might see if you can adapt it to your requirements.
http://www.sqlmag.com/Article/ArticleID/49827/sql_server_49827.html
October 5, 2006 at 6:18 am
I appreciate the article, but I'd like to do this on the report side. I'm close, but as stated above am running into problems having the members of the group populate my array the first time the median function needs to be called.
If what I'm saying doesn't make sense, then does anyone have an idea as to how to create their own Sum() function? (one that will take the sum of entries in a group)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply