Oracle Nested Query with Group By and Having Clauses

Question

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.

Answer 1

If I understand correctly:

select *
 from (SELECT D.DID, AVG(F.DIST) as AvgDist 
 from Destinations D 
 join Flights F ON F.DID = D.DID 
 GROUP BY D.DID) avgdist
 join
 (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) sumdist
 on avgdist.did = sumdist.did
 where avgdist.avgdist < sumdist.fDist;