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

  • adamxtokyo
     + 15 comments

    I'm no SQL expert, but I feel like my solution is more readable and it works for even/uneven table records:

    SET @r = 0;

SELECT ROUND(AVG(Lat_N), 4)
FROM (SELECT (@r := @r + 1) AS r, Lat_N FROM Station ORDER BY Lat_N) Temp
WHERE
    r = (SELECT CEIL(COUNT(*) / 2) FROM Station) OR
    r = (SELECT FLOOR((COUNT(*) / 2) + 1) FROM Station)

    First, I create a numbered, ordered, table (doesn't matter if ASC or DESC). Then, I calculate the midpoint (either 1 or 2 rows) and fetch only those rows. After that, I just take the average of the rows fetched and voila! =)