CODEWITHSUNDEEP
sqldatabaseoracle-database
sealsix
sealsix

Reputation: 73

Oracle SQL - Nested Query with Grouping and Having clauses

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. 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.

Upvotes: 0

Views: 654

Answers (1)

Multisync
Multisync

Reputation: 8797

It's not difficult with analytic functions:

SELECT DISTINCT AID FROM (
    SELECT F.AID, D.DID, 
           AVG(F.DIST) OVER (PARTITION BY D.DID) as AvgDist, 
           SUM(F.DIST) OVER (PARTITION BY F.AID, D.DID) as AirDistance
    FROM Destinations D join Flights F ON F.DID = D.DID
) WHERE AirDistance > AvgDist;

With your subqueries it should be something like this:

SELECT DISTINCT a.AID 
FROM
    (SELECT D.DID, AVG(F.DIST) as AvgDist 
     FROM Destinations D join Flights F 
         ON F.DID = D.DID GROUP BY D.DID
    ) d  
    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
    ) a
    ON d.DID = a.DID 
WHERE fDist > AvgDist;

Upvotes: 1

Related Questions