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

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.

-
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.

-