CODEWITHSUNDEEP
sqloracle-database
Aayush Chachan
Aayush Chachan

Reputation: 27

Finding median from sql query

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:

enter image description here

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

I wrote the query as:

select round(LAT_N,4) from (select LAT_N from station order by LAT_N) where ROWNUM = ((LENGTH(LAT_N)+1)/2);

I am not getting the output from this query. Can anyone explain what's the error?

Upvotes: 0

Views: 1959

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

In Oracle, probably the simplest way is to use median():

select median(LAT_N) 
from stations;

Upvotes: 3

Dragan Jovanović
Dragan Jovanović

Reputation: 181

Try it this way :

select top 1 a.LAT_N, count(*) biggerThan
from
(select LAT_N from station) a join (select LAT_N from station) b on a.LAT_N > b.LAT_N
    join (select count(*) countt from station) counterr on 1 = 1
group by a.LAT_N, counterr.countt
having count(*) > counterr.countt/2 - 1
order by biggerThan

It returns the smallest value which is bigger than approximately half of set members. I have defined "approximately half" as countt/2 - 1, and you should probably be more precise with this part of logic.

Upvotes: 0

Related Questions