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

  • michaeljsiena
     + 1 comment

    I've only been learning SQL for 2 weeks but here's my solution:

    WITH LAT_With_Row_Info AS (
    SELECT 
        LAT_N,
        ROW_NUMBER() OVER(ORDER BY LAT_N) AS row_num,
        (SELECT COUNT(*) FROM Station) AS tot_rows
    FROM Station
)

SELECT ROUND(AVG(LAT_N), 4) AS median
FROM LAT_With_Row_Info
WHERE (
    CASE 
        WHEN tot_rows % 2 = 0
        THEN row_num IN (tot_rows / 2, (tot_rows / 2) + 1)
        ELSE row_num = (tot_rows + 1) / 2
    END
);

    Is there a better way to get the total number of rows? Would a temporary variable be better? Also, while using AVG() works just fine in both odd and even cases, it might be a little difficult to understand at a glance.