Problem with this query

Discussion in 'MySQL' started by Roman10, Dec 2, 2013.

0
  1. #1
    Hi

    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

    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);
     
    Roman10, Dec 2, 2013 IP
  2. sarahk

    sarahk iTamer Staff

    Messages:
    28,832
    Likes Received:
    4,541
    Best Answers:
    123
    Trophy Points:
    665
    #2 
    select model.mid, model.name, count(flight.fid) as flightcount, AVG(flight.passengerCount) as avgPC
from flight, model, airplane
where flight.aid = airplane.aid and airplane.mid = model.mid
GROUP BY model.mid
order by avgPC
    Code (markup):
    give that a go

    or set up some sample data in http://sqlfiddle.com/ for us to play with
     
    sarahk, Dec 5, 2013 IP
    khodem likes this.
  3. khodem

    khodem Well-Known Member

    Messages:
    206
    Likes Received:
    8
    Best Answers:
    3
    Trophy Points:
    120
    #3
    Yeah better setup a fiddle that way it's easy to create many version of SQL that do the same job :d
     
    khodem, Dec 18, 2013 IP