December 2nd, 2013, 06:56 PM
Does my query need to fix!!
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);
December 3rd, 2013, 02:55 AM
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)
SELECT Model.mid, Model.name, fa.FlightsPerModel
(SELECT Airplane.mid AS AirMidm, COUNT(Flight.fid) AS FlightsPerModel
INNER JOIN Airplane ON Airplane.aid = Flight.aid
GROUP BY Airplane.mid
) fa ON fa.AirMidm = Model.mid