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;