Hi Tom,
It does not work with duplicated values: with 1 2 2 4 5 the answer should be 2.
If you want to return a value present in the set, you can define a new median, medianEx, that
return the min value producing a count of at least 50% of all the values below or equal to it:
SELECT Min(Number) As MedianEx
FROM Number As N
WHERE (SELECT COUNT(*) FROM Number As N1 WHERE N1.Number <= N.Number)
>= 0.5*(SELECT COUNT(*) FROM Number)
which return a value with odd number of value, even number of value or duplicated values, and a
result "close" to what the classical median is.
Vanderghast, Access MVP
"Tom Ellison" <(EMail Removed)> wrote in message
news:(EMail Removed)...
> On Wed, 9 Jul 2003 04:54:21 0700, "Jeff Boyce" <(EMail Removed)>
> wrote:
>
> >Hi Tom!
> >
> >As a recovering statistician, I'd offer the notion that the median of a even
> >number of elements is the average of the two "centermost".
> >
> >Jeff Boyce
> ><Access MVP>
>
> Dear Jeff:
>
> This is very true except . . .
>
> I was preparing for the other shoe to drop. Not infrequently, the
> request is to show the entire record that is at the median, not just
> the numeric value of the median. If there are an even number of rows
> with distinct median values, then there is no median row because, if
> you average the values in the two rows at the center you would get a
> value that doesn't exist in the table.
>
> There are variations of what to show depending on uniqueness of the
> values and whether there are an odd or even number of values, all of
> this depending on the needs for the situation at hand.
>
> The query I gave was what I use for a starting point to prepare any of
> these variations. As a starting point, I'll stand by it.
>
> Tom Ellison
> Microsoft Access MVP
> Ellison Enterprises  Your One Stop IT Experts
