Re: Median function in SQL
Date: 1998/03/20
Message-ID: <6euub4$itq$1_at_nnrp1.dejanews.com>#1/1
You're right about the little math gap. This script should deal with both even and odd numbered lists.
select avg(ranked.revenue)
from (select 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) ranked, (select count(*) median_point from companys) mp where (mp.median_point/2 != round(mp.median_point/2) and ranked.ranking = round(mp.median_point/2)) or (mp.median_point/2 = round(mp.median_point/2) and ranked.ranking in (mp.median_point/2,(mp.median_point/2)+1)))
or you could replace the where clause with a more efficient but somewhat less readable decode statement like this:
where 1 = decode(mp.median_point/2,
round(mp.median_point/2), decode(ranked.ranking, mp.median_point/2, 1, (mp.median_point/2)+1, 1, 0), decode(ranked.ranking, round(mp.median_point/2), 1, 0))
share and enjoy.
tzad
uncle tzad's house o' SQL
In article <6emahj$kkb$1_at_nnrp1.dejanews.com>,
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
> > >
-----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/ Now offering spam-free web-based newsreading Received on Fri Mar 20 1998 - 00:00:00 CET