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

  • After reading the meta for a bit it seems there is a division as to weather this question should be here or at SO. I recognize this might not be a ninja level query, but it's got me stumped and there is not DBA at the company I work for (6 people total). – JIStone May 4 '11 at 19:55

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.

| improve this answer | |
  • I agree. When worked into a large query the extra lines just make the whole thing more cumbersome & hard to debug/maintain. – JIStone May 5 '11 at 15:31

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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.