Re: How do I get the median instead of average?

[laptop alias]

Procedures for calculating both median and mode are provided at http://www.artfulsoftware.com/infotree/queries.php?&bw=1276#444

...although it seems to me that you could also do it this way...

SELECT AVG(i) median 
  FROM
     ( SELECT t1.*
            , COUNT(*) rank
            , (SELECT CEILING(COUNT(*)/2) FROM my_table) mid 
         FROM my_table t1 
         JOIN my_table t2 
           ON t2.i <= t1.i 
        GROUP 
           BY t1.i 
       HAVING rank = mid
        UNION
       SELECT t1.*
            , COUNT(*) rank
            , (SELECT CEILING(COUNT(*)/2) FROM my_table) mid 
         FROM my_table t1 
         JOIN my_table t2 
           ON t2.i >= t1.i 
        GROUP 
           BY t1.i 
       HAVING rank = mid
     ) x;

I'll get it right eventually :-)



Edited 4 time(s). Last edit at 06/30/2010 06:29AM by laptop alias.