Re: Median function in SQL

gharabedian_at_24hourfit.com wrote:
>
> This does work when n (count) is an odd number but when n is an even number
> shouldn't the two values in the middle (n/2 and (n/2)+1) be averaged for the
> median? How would this be done?
>
> Thanks,
> Greg
>
> In article <3503C953.2A3C_at_pcm.bosch.de>,
> Angelo Cavallaro <angelo.cavallaro_at_pcm.bosch.de> wrote:
> >
> > Hi.
> >
> > I've tried it and it works with this group by:
> >
> > select ranked.company, ranked.revenue
> > from (select a.company, a.revenue, count(*) ranking
> > from companys a, companys b
> > where a.revenue>b.revenue
> > or (a.revenue=b.revenue and a.rowid >= b.rowid)
> > group by a.rowid, a.revenue, a.company) ranked,
> > (select round(count(*)/2) median_point
> > from companys) mp
> > where ranked.ranking=mp.median_point
> >
> > Great thing !!!
> > The first inline-view seems to me to be a solution
> > for many ordered rownum queries!
> >
> > Greez,
> > Angelo.
> >
> > tzadkiel wrote:
> > >
> > > this was fun. one solution is like this:
> > >
> > > select ranked.company, ranked.revenue
> > > from (select a.company, a.revenue, count(*) ranking
> > > from companys a, companys b
> > > where a.revenue>b.revenue
> > > or (a.revenue=b.revenue and a.rowid>=a.rowid)) ranked,
> > > (select round(count(*)/2) median_point
> > > from companys) mp
> > > where ranked.ranking=mp.median_point
> > >
> > > the first in-line view is a general way to do rankings - what we all
> > > intuitively think "rownum" should do, but doesn't. once the table is
> > > ranked, it's simple for the second view to locate what the median record
> > > number is.
> > >
> > > this is obviously table/field specific, but we should be able to use the
> > > technique with the dynamic SQL packages to give us a general function
> > > something like :
> > >
> > > "MEDIAN(table_name, field_name)"
> > >
> > > share and enjoy.
> > >
> > --
> > -----------------------------------------------------------------
> > Angelo Cavallaro / / / \ TECTUM Beratungsgesellschaft
> > ORACLE-SW-Berater / / / \ für Informationsverarbeitung
> > ancavall.NOSPAM / / /-------\ Tel: 0711/99073-10
> > _at_aol.com /__/__/_________\ Fax: 0711/99073-99
> >
> > -----------------------------------------------------------------
> > Visit the Home of the Green Ribbon Campaign:
> >
> > http://www.ponce.oisoft.com/
> >
> > -----------------------------------------------------------------
> >
>
> -----== Posted via Deja News, The Leader in Internet Discussion ==-----
> http://www.dejanews.com/ Now offering spam-free web-based newsreading

In their book "Oracle SQL 101 Frequently Asked Questions", Lewis and Sirota discuss the methods to obtain the median in great length.

-- 
Hope this helps !
Regards,
Srini
(to reply via email, please change "nospam" to "chavali" in my email
address -  :-)  )