How do I find the median value of a column in MySQL?

Question

I can only imagine doing this with two database queries. The first finds the number of rows in the database, and the second selects with an ORDER BY on the column I am interested in and LIMIT X, 1 where X is half the number of rows.

Is there a simple way to do this with only one query?

Right now I am using averages in my calculations, but I think the mean would be better; there is no upper bound to the values and they are bounded from below by 0.

---

EDIT: yes, I meant to say 'median' but was having some brain error & searched for 'mean'. I have now found the answer over at stackoverflow

Answer 1

There is quite a bit of discussion here on calculating median values from a MySQL table. Just search the page for 'median'.

As an aside, it strikes me as remiss that there is no built-in function to do this. Median is often more descriptive of central tendency than mean. Access/VBA has the same hole in its function list.

Answer 2

I haven't seen a solution anywhere that manages to get the median in a single query. I don't mind temp tables, but if they're not necessary, great! Here's what I came up with:

SELECT AVG(profit) median, nofitems FROM(
  SELECT x.profit, SUM(SIGN(1.0-SIGN(y.profit-x.profit))) diff, count(*) nofitems, floor(count(*)+1/2)
  FROM brand_prof x, brand_prof y
  GROUP BY x.profit
  HAVING SUM(SIGN(1.0-SIGN(y.profit-x.profit))) = floor((COUNT(*)+1)/2)
      OR SUM(SIGN(1.0-SIGN(y.profit-x.profit))) = ceiling((COUNT(*)+1)/2)
) x;

I tested this for an even set, and got the right answer. brand_prof is just two columns: brand_name, and profit, a decimal value. If this were integer values, you may have to cast "ceiling((CAST COUNT(*) AS DECIMAL)..." More than I've tested. The cool idea to use a cartesian product and relate the sum of the signs was not mine. I have forgotten the author, unfortunately.