I need to find the airport(departure or arrival) that had the most passengers in the last year.
The tables are:
PASSENGER(id,name,surname) TAKES(id_passenger,id_flight) FLIGHT(id,departure_id,arrival_id,departure_date,arrival_date,id_plane) AIRPORT(id,name,city,country) PLANE(id,type,capacity)
What I think I need to do is:
Count the number of passengers each airport has and return that but I do not know how to pick the one with the most passengers among those two. Do I need to use nested select queries? Would it be useful to use something like
WITH maxdep as SELECT ( max passengers from departure airport) maxar as SELECT ( max passengers from arrival airport) select max (val1, val2), from ... ?
How do I return just one?
EDIT: I was thinking of a case select from the two previous with statements. Can that work?
WITH departures as (SELECT departure_id, count(id_passenger) from passenger, takes, flight WHERE passenger.id=takes.id_passenger and takes.id_flight=flight.id and flight.departure_date between '01-JAN-2013' AND '01-JAN-2014' group by 1 order by 2 desc where ROWNUM <2) arrivals as (SELECT arrival_id, count(id_passenger) from passenger, takes, flight WHERE passenger.id=takes.id_passenger and takes.id_flight=flight.id and flight.arrival_date between '01-JAN-2013' AND '01-JAN-2014' group by 1 order by 2 desc where ROWNUM <2) SELECT CASE WHEN departures.count > arrivals.count THEN departures.departure_id WHEN departures.count < arrivals.count THEN arrivals.arrival_id ELSE departures.departure_id || 'has the same number of visitors as'|| arrivals.arrival_id END;
I am sorry if this is messed up
Will this work?