Re: Median and Geometric Mean in Oracle?

From: Connor McDonald <connor_mcdonald_at_yahoo.com>
Date: Thu, 11 Nov 1999 19:23:32 +0800
Message-ID: <382AA734.69D9@yahoo.com>

Jonathan Lewis wrote:
>
> Median is difficult, it's pretty much a procedural
> thing, so I'll skip that one (for the moment at least)
>
> Is the geometric mean the:
> reciprocal of the average of the reciprocals
> or
> the Nth root of the product of N values ?
>
> I think the former would be:
> select 1/(sum(1/col)) from tab;
>
> and the latter could be
> select exp( sum( ln(col) ) / count(col) ) from tab
>
> --
>
> Jonathan Lewis
> Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk
>
> david_petit_at_yahoo.com wrote in message <80d4pj$q83$1_at_nnrp1.deja.com>...
> >
> >I want to calculate the median and G. mean of a group of values. Did
> >Oracle provide these two functions?
> >
> >If not, could anyone tell me how to get the median and G. mean of a
> >group of numbers?

select	x.total
from	table x, table y
where	(appropriate joins)

group by x.total
having
  sum(sign(1-sign(y.total-x.total))) in

       ( trunc((count(*)+1)/2), trunc((count(*)+2)/2) );

or something like that should give the median... --

---

Connor McDonald
"These views mine, no-one elses etc etc" connor_mcdonald_at_yahoo.com

"Some days you're the pigeon, and some days you're the statue." Received on Thu Nov 11 1999 - 05:23:32 CST