Take the 2-minute tour ×

I'm trying to count median for prices. I founded answer how to do it here- Simple way to calculate median with MySQL, but it doesn't work for me, I get empty result. Can anyone help?

SELECT x.price from mediana as x, mediana y
GROUP BY x.price
HAVING SUM(SIGN(1-SIGN(y.price-x.price))) = (COUNT(*)+1)/2
share|improve this question
Can you demonstrate this with a sqlfiddle? –  Jack Mar 11 '13 at 12:32
Have you tried most voted (not accepted) answer from the linked question? Example of execution –  default locale Mar 11 '13 at 12:40
sqlfiddle.com/#!2/b3fe7e/1 –  Alex K Mar 11 '13 at 12:44
@defaultlocale, thanks, it works, but a little bit not as I expected. If I have price - 1,2,3,4 median should be 2.5, but I get 3 –  Alex K Mar 11 '13 at 13:00
@AlexK Yes it doesn't handle even number of records (mean of 2 middle elements). Try this I haven't tested it, though. –  default locale Mar 11 '13 at 13:11
show 3 more comments

1 Answer

up vote 1 down vote accepted

AFAIU your question.

This answer by @velcrow calculates median value successfully. Unfortunately when there is even number of rows instead of calculating the mean value of 2 middle rows query just returns second value. I've made a couple of modifications to the query to fit your needs:

--average value for middle rows
SELECT avg(t1.price) as median_val FROM (
SELECT @rownum:=@rownum+1 as `row_number`, d.price
  FROM mediana d,  (SELECT @rownum:=0) r
  -- put some where clause here
  ORDER BY d.price
) as t1, 
  SELECT count(*) as total_rows
  FROM mediana d
  -- put same where clause here
) as t2
--this condition should return one record for odd number of rows and 2 middle records for even.
AND t1.row_number>=total_rows/2 and t1.row_number<=total_rows/2+1; 

Test on sample data on sqlfiddle

share|improve this answer
this returns wrong result , it return 2 –  echo_Me Mar 11 '13 at 13:35
For prices 1,2,3,4 it returns - 2.5, for 1,2,3,4,4 returns 3, so it's correct ;) –  Alex K Mar 11 '13 at 13:40
add comment

Your Answer


