0

I need to expand on the first query by counting the number of flights from the citys in
my airline database and so far have come up with the second query;

The tables in the database are flight, and city.
flight columns:number, origincityid,destinationcityid,departure,duration and stops
city columns:id and name

SELECT city.name, flight.origincityid FROM city
LEFT JOIN flight ON city.id=origincityid


SELECT city.name, COUNT(flight.id) AS numflight
->FROM city LEFT JOIN flight ON origincityid=origincityid.id
->GROUP BY destinationcityid;

2
Contributors
1
Reply
2
Views
7 Years
Discussion Span
Last Post by tesuji
0

Hi

Despite whether your below select statement meets your requirments, it has a severe fault:

SELECT city.name, COUNT(flight.id) AS numflight
->FROM city LEFT JOIN flight ON origincityid=origincityid.id
->GROUP BY destinationcityid;

That is, all selected columns not being parameters of aggregate functions (e.g. count) must be enumerated in GROUP BY clause no matter mysql manuals are alleging the opposite. Therefore your GROUP BY clause should be: GROUP BY city.name (everything else is comparing apples and oranges).

Ah, what about your former thread "Trying to join 2 tables in mysql-Help", really solved ?

-- tesu

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.