0

I'm trying to solve weather observation 20 from HackerRank using MYSQL and I've written the following code 

SET @row1 = 0, @row2 = 0;

SELECT IF(COUNT(LAT_N) MOD 2 = 0, 

          -- Ture clasue 
          (SELECT ROUND(t.LAT_N,4) 
          FROM (SELECT station.LAT_N, @row1:= @row1 + 1 AS row1 FROM station GROUP BY station.LAT_N) AS t
          WHERE t.row1 = ROUND(COUNT(t.row1) DIV 2,0)
          ), 

         -- False Clause
         (SELECT ROUND(AVG(t.LAT_N),4)
          FROM (SELECT station.LAT_N, @row1:= @row1 + 1 AS row1, @row2:= COUNT(LAT_N) - 1 AS row2 FROM station GROUP BY station.LAT_N) AS t
         WHERE t.row1 = ROUND(COUNT(t.row1) DIV 2,0) - 1
                        OR
               t.row2 = ROUND(COUNT(t.row2) DIV 2,0) + 1
         )
         )
FROM station;

but i'm getting this error

ERROR 1111 (HY000) at line 3: Invalid use of group function

can you please help me rectify.

Exteam
  • 98
  • 7
codeyk
  • 1
  • 2
    Help us help you - share the requirements, table structure, some sample data and the result you're trying to get for this sample. – Mureinik Jan 05 '20 at 09:12
  • The hackerrank question is about median calculation try googling mysql median. – P.Salmon Jan 05 '20 at 10:00
  • Does this answer your question? [Simple way to calculate median with MySQL](https://stackoverflow.com/questions/1291152/simple-way-to-calculate-median-with-mysql) – P.Salmon Jan 05 '20 at 10:01
  • Hey P.Salmon, I understand there are simpler ways of doing this but I want to understand what is the error in my code and how do I rectify it. – codeyk Jan 05 '20 at 16:15
  • @Mureinik - I'm trying to solve a [this](https://www.hackerrank.com/challenges/weather-observation-station-20/problem) problem from hackerrank. All table structures and the requirements are mentioned in the link. – codeyk Jan 05 '20 at 16:17

1 Answers1

0

I have solved the problem on my own with the following code. 

SET @row1 = 0;

SELECT ROUND(LAT_N,4)
FROM (SELECT t.row1, s.LAT_N FROM station AS s
     INNER JOIN (SELECT ID, @row1 :=@row1 + 1 AS row1 FROM station ORDER BY LAT_N) AS t ON s.ID=        t.ID) AS t2 
WHERE row1 = (SELECT ROUND(COUNT(LAT_N)/2,0) FROM station);

This is not optimum but works and for the benefit of others here's what I have learned so far trying to solve the code

  1. I tried to solve the problem with the logic from this youtube video but the code in the video is for SQL Server whereas I'm using MySQL
  2. HackerRank tests for only odd set of inputs which is a lot easier than finding median for an even set (here I've limited my code to odd set)
  3. Being a newbie I was unaware of the order of execution in MySQL and bundled up a bunch of code which I thought was required. I was able to resolve the error by changing from WHERE clause to HAVING clause
  4. Code posted in the questing completely ignores the ORDER BY clause which is the key logic behind the solution (as mentioned in the video) and the reason why I switched to JOIN (to retain the logic even though there are simpler solution)
codeyk
  • 1