#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2013
    Posts
    1
    Rep Power
    0

    Red face Does my query need to fix!!


    Hi guys

    I have 3 tables which are (flight, model, airplane)
    Flight: fid, pid, aid, plannedDeparture, actualDeparture, passengerCount, destination
    Model: mid, name
    Airplane: aid, mid, oid, hid, dateAqu

    and I want to get a report to show the total number of flights and the average passenger count for all completed flights grouped by Model.id and sorted by the average passenger count. Include Model.name into this report.


    I tried this query, but it doesn't work. it made me crazy. I hope if there is someone can help me here

    select distinct flight.fid, flight.aid, flight.pid, flight.plannedDeparture, flight.actualDeparture,
    flight.passengerCount, flight.destination, airplane.aid, airplane.mid, model.mid, model.name
    from flight, model, airplane
    where flight.aid = airplane.aid and airplane.mid = model.mid
    GROUP BY model.mid
    and (select COUNT(flight.fid) as `count`, AVG(flight.passengerCount) as `average` FROM flight);
  2. #2
  3. Lord of the Dance
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Oct 2003
    Posts
    3,535
    Rep Power
    1906
    DISTINCT and GROUP BY is different syntax for the same function. You should only use one of them.

    It is better to use the explicit JOIN syntax, e.g.:
    SELECT field FROM tableA INNER JOIN tableB ON TableA.id = TableB.ud

    To get the model name and flights per model, you should be able to use something like this: (untested)
    sql Code:
     
    SELECT Model.mid, Model.name, fa.FlightsPerModel
    FROM Model
    INNER JOIN 
    	(SELECT Airplane.mid AS AirMidm, COUNT(Flight.fid) AS FlightsPerModel
    	 FROM Flight
    	 INNER JOIN Airplane ON Airplane.aid = Flight.aid
    	 GROUP BY Airplane.mid
    	) fa ON fa.AirMidm = Model.mid

IMN logo majestic logo threadwatch logo seochat tools logo