[SOLVED] Median not working correctly

[MichaelMackey]

Hi, I'm having trouble understanding how to get this median to display correctly.  I found script that works for a Median but my problem is I need to select from multiple tables and that seems to be whats screwing up my query. 

 

This is the query I'm trying to find the median of:

 

SELECT Total FROM kn_contestant 
INNER JOIN kn_results_contestant ON kn_contestant.idcontestant = kn_results_contestant.idcontestant 
WHERE kn_contestant.school_grade = 5 
ORDER BY Total desc

 

This is what I'm using to do it, but it retrieves 3 identical results and they're noticeably higher than they should be.

 

SELECT Total Median FROM
(SELECT a1.idcontestant, a1.Total , COUNT(a1.Total ) Rank
FROM kn_results_contestant a1 INNER JOIN kn_contestant b1 ON b1.idcontestant = a1.idcontestant, 
kn_results_contestant a2 INNER JOIN kn_contestant b2 ON b2.idcontestant = a2.idcontestant
WHERE a1.Total < a2.Total OR (a1.Total = a2.Total AND a1.idcontestant <= a2.idcontestant)
AND b1.school_grade = 5 
AND b2.school_grade = 5
GROUP BY a1.idcontestant, a1.Total
ORDER BY a1.Total desc) a3
WHERE Rank = (SELECT (COUNT(*)+1) DIV 2 FROM kn_results_contestant)

 

I would guess there is some easy solution to this, but I've never gone into this type of select before and it's really been a time waster trying to figure out so far.  Any help is appreciated, thanks.

 

Mike

[fenway]

Did you run the inner query alone to see if the output makes sense?