We use cookies to ensure you have the best browsing experience on our website. Please read our cookie policy for more information about how we use cookies.
  1. Prepare
  2. SQL
  3. Aggregation
  4. Weather Observation Station 20
  5. Discussions

Weather Observation Station 20

  • piot4rek
     + 0 comments

    @ankitraheja Here's what I came up with.

    SELECT ROUND(AVG(S.LAT_N), 4) FROM STATION S WHERE
ABS((SELECT COUNT(*) FROM STATION WHERE LAT_N < S.LAT_N) -
(SELECT COUNT(*) FROM STATION WHERE LAT_N > S.LAT_N)) <= 1;

    It's very similar to one of the top comments here, but it should work in both cases (number of rows even or odd).

    It takes the average of rows for which the number of smaller values and the number of greater ones do not differ by more than 1. In case of even number of rows, for the two values in the "middle" this difference equals 1, so the resulting median will be their avarage. In case of odd number of rows, it will do the job as well.

    However, this solution is still not universal - if there are more than one rows containing the median value, this will still fail - we should then find the minimum value of that expression in my query, so it might complicate things a bit more.