Q:

How to write a query to find Median of column in a Table in MySql?

I was trying to solve problem in Hackerrank SQL Practice section and stuck in Problem 'Weather Observation Problem 20'.
To find Median, I though of the following approach:

sub-query to count the lower half of the entries.
sub-query to count the upper half of the entries.
Equate these queries together under a WHERE clause (so that an entry has the same number of entries before and after).

QUERY:
select round(s.lat_n,4) 
from station s 
where (
        select round(count(s.id)/2)-1 
        from station
    ) = (
        select count(s1.id) 
        from station s1 
        where s1.lat_n > s.lat_n
    );

PLEASE HELP ME OUT WITH THE OPTIMIZED QUERY.
LINK OF PROBLEM STATEMENT : https://www.hackerrank.com/challenges/weather-observation-station-20/problem

A:

When you sort the values the median will be either exactly in the middle (odd number of rows) or the average of two values around the middle (even number of rows). For these values the following is true:

at least half of all values (including itself) are equal or less
at least half of all values (including itself) are equal or greater

When you find that/those values (let's call them candidates), you will need the average of distinct candidate values.
The abouve can be expressed with the following query:
select round(avg(distinct lat_n), 4) as median_lat_n
from station s
cross join (select count(*) as total from station) t
where t.total <= 2 * (select count(*) from station s1 where s1.lat_n <= s.lat_n)
  and t.total <= 2 * (select count(*) from station s1 where s1.lat_n >= s.lat_n)

Note that this is a quite slow solution for big tables.

