0

Having the following requirements: A median is defined as a number separating the higher half of a data set from the lower half. Query the median of the Northern Latitudes (LAT_N) from STATION and round your answer to decimal places.

Input Format

The STATION table is described as follows:

Field : Type
ID    : NUMBER
CITY  : VARCHAR2(21)
STATE : VARCHAR2(2)
LAT_N : NUMBER
LONG_W: NUMBER

where LAT_N is the northern latitude and LONG_W is the western longitude.

I tried to use the over clause to create a single sorted window, with a kind of an "index" that can be used to find the median:

SELECT AVG(SUBQUERY.LAT_N)
FROM
(SELECT ROW_NUMBER()OVER(ORDER BY LAT_N ASC) -1  as MY_INDEX,LAT_N
FROM STATION) AS SUBQUERY
HAVING SUBQUERY.MY_INDEX IN (FLOOR(MAX(SUBQUERY.MY_INDEX)/2), CEIL(MAX(SUBQUERY.MY_INDEX)/2))

I also tried:

SELECT AVG(SUBQUERY.LAT_N)
FROM
(SELECT ROW_NUMBER()OVER(ORDER BY LAT_N ASC) -1  as MY_INDEX,LAT_N
FROM STATION) AS SUBQUERY
HAVING SUBQUERY.MY_INDEX IN (FLOOR((COUNT(SUBQUERY.LAT_N)-1)/2), CEIL((COUNT(SUBQUERY.LAT_N)-1)/2))

When using specific values inside the condition, the behavior is the one desired e.g.:

SELECT AVG(SUBQUERY.LAT_N)
FROM
(SELECT ROW_NUMBER()OVER(ORDER BY LAT_N ASC) -1  as MY_INDEX,LAT_N
FROM STATION) AS SUBQUERY
WHERE SUBQUERY.MY_INDEX IN (5,6)

With this I get the average LAT_N value of rows 5 and 6

What is it that I am not considering and is making the Query get the following error?

ERROR 1054 (42S22) at line 4: Unknown column 'SUBQUERY.MY_INDEX' in 'having clause'
RuloRifa
  • 1
  • 1
  • never mix aggregate functions like count/avg with row values (like your produced MY_INDEX). try using a count window function instead: `select avg(lat_n) from (select lat_n, abs((count(1) over ()+1)/2-row_number() over (order by lat_n)) < .6 is_median from station) x where is_median` – ysth Mar 28 '23 at 00:40

0 Answers0