Function for finding the median in T-SQL?

18 Aug 2006 4:40 AM

JP

I have a sql statement that returns the last four quarters of sales
numbers for a given salesperson. What I would like to do is exclude the
highest figure and the lowest figure then sum the two middle figures and
divide by two.

I was thinking a median function would help but couldn't find anything
in BOL about it. Can anyone suggest a way for me to accomplish this?
Thanks.

*** Sent via Developersdex http://www.developersdex.com ***

18 Aug 2006 4:54 AM

Steve Kass

JP,

If you search groups.google.com for sqlserver and median, you will
find a number of solutions. For this question, with only four values
to start with, you can use something like this:

select
salespersonid,
(sum(sales) - max(sales) - min(sales))/2.0 as medianSales
from salestable
group by salespersonid

To be safe you might want to add
having count(*) = 4
to avoid an incorrect result if some salesperson doesn't have all
4 quarters of results.

Steve Kass
Drew University
www.stevekass.com

Show quote

"JP" <jp@nospam.com> wrote in message news:efW5yBowGHA.560@TK2MSFTNGP05.phx.gbl...
>I have a sql statement that returns the last four quarters of sales
> numbers for a given salesperson. What I would like to do is exclude the
> highest figure and the lowest figure then sum the two middle figures and
> divide by two.
>
> I was thinking a median function would help but couldn't find anything
> in BOL about it. Can anyone suggest a way for me to accomplish this?
> Thanks.
>
>
>
> *** Sent via Developersdex http://www.developersdex.com ***

18 Aug 2006 5:00 AM

Chris Lim

JP wrote:

> I have a sql statement that returns the last four quarters of sales
> numbers for a given salesperson. What I would like to do is exclude the
> highest figure and the lowest figure then sum the two middle figures and
> divide by two.
>
> I was thinking a median function would help but couldn't find anything
> in BOL about it. Can anyone suggest a way for me to accomplish this?
> Thanks.

There's no median function. In your case, since you are only dealing
with a set of 4 numbers, I think the easiest think would be to rank
each quarter and then get the quarter ranked 2 and 3.

Getting a true median that will work over any range of data is more
tricky. e.g. See
http://databases.aspfaq.com/database/how-do-i-calculate-the-median-in-a-table.html