0

I was learning about calculating the median value in MySQL. I looked up this code solution to calculate the median value of lat_n variable.

set @ct := (select count(1) from station);
set @row_id := 0;

select avg(lat_n) as median
from (select * from station order by lat_n)
where (select @row_id := @row_id + 1)
between @ct/2.0 and @ct/2.0 + 1;

But I get an error: " ERROR 1248 (42000) at line 4: Every derived table must have its own alias"

I know this error usually occurs when I have sub-query statements with no alias, which I see two in my code, but I have never implemented this with declared variables before.

And for the bonus question... how can I approach this problem as someone who is used to PostgreSQL and using CTEs?

This is my first time posting a question on S.O.!!!

Thanks for the help!

EDIT 1: This question is from HackerRank: https://www.hackerrank.com/challenges/weather-observation-station-20/problem?h_r=next-challenge&h_v=zen

I get it to work with no error by using this:

set @ct = (select count(1) from station);
set @row_id = 0;

select avg(ordered.lat_n) as median
from (select * from station order by lat_n) as ordered
where (select @row_id := @row_id + 1)
between @ct/2.0 and @ct/2.0 + 1;

But it now says I got the wrong answer (I calculated a value w/o an error but it is wrong) so I am confused if the code is good to go?

EDIT 2: Per @ysth suggestion. The following code also works in getting rid of the error and provides the same value of 83.89 as does the code in EDIT 1. But apparently it is not the correct answer. I'm going to close this question but if anyone notices a problem in the syntax of the code to calculate a median value of a variable, then please let me know.

The code:

with ordered as(
    select lat_n, 
    row_number() over(order by lat_n) as row_id,
    (select count(1) from station) as ct
    from station
)

select avg(lat_n) as median
from ordered
where row_id between ct/2 and ct/2 +1
fkn_ez
  • 55
  • 1
  • 9

0 Answers0