I am relatively new to MySQL, and I'm trying to write a function to find the median of a list of latitudes. I found a couple different solutions online to this problem, but I couldn't really understand what they were doing. I decided to make something a little more easily readable and intuitive, but I seem to be running into some errors. Basically, the method finds the minimum of the biggest half of numbers, and the maximum of the bottom half, and averages them out. This should cover both cases of even and odd lengths.
I tried testing every piece of code and for some reason I think all my errors are coming from the 6th line of code where I try to find the middle value. My case needs to cover both even and odd length of lists which is why I have both med1 and med2.
Here is my current code:
SET @med1 = (SELECT min(lat_n)
FROM station
WHERE lat_n >= (SELECT lat_n
FROM station
ORDER BY lat_n ASC
LIMIT FLOOR((SELECT COUNT(*)/2 FROM station)), 1
)
);
SET @med2 = (SELECT max(lat_n)
FROM station
WHERE lat_n <= (SELECT lat_n
FROM station
ORDER BY lat_n DESC
LIMIT FLOOR((SELECT COUNT(*)/2 FROM station)), 1
)
);
select round((@med1 + @med2)/2,4)
and the error message:
ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '((SELECT COUNT(*)/2 FROM station)), 1
I have checked each little 'bit' of code (the select count(*), the floor, the limit) on its own but for some reason it doesn't work when it's all together. The output should be the median of the column lat_n in the table station but I'm getting this error instead. Thank you very much in advance.
