So here's the question
Find the IDs of aircraft which have flown more distance for a destination than the average distance of all flights for that same destination
Here is the schema
Destinations(did: integer, dname: string, state: string)
Aircraft(aid: integer, aname: string, color: string)
Flights(aid: integer, did:integer, fdate:date, distance: real)
So far I have been able to find answers to two different parts of this query.
I have found the average distance flown to each destination and I have also found the total distance flown to each location by each aircraft.
The queries for the above are listed below:
SELECT D.DID, AVG(F.DIST) as AvgDist from Destinations D join Flights F ON F.DID = D.DID GROUP BY D.DID; SELECT F.AID,F.DID, SUM(F.DIST) as fDist from Flights F join Destinations D ON F.DID = D.DID GROUP BY F.AID,F.DID;
My Problem is I do not see how to combine them to find the solution. I know all I need to do is figure out a way to check if the average distance of that destination is less than the distance flown to by that individual aircraft but i'm just not sure how to proceed. All my Having clauses seem to not work so i'm sort of stuck for now.