Re: Median function in SQL

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 CST