aggregate function problem - MySQL

[ros]

Hi,

I have 2 tables ( cities and airports) in a database and I want to run
a query that gives me the city with the largest no. of airports. But I
am a little confused how to do it.

I used this, although it doesn't make much sense to me:
select count(c.name)
from airports a, cities c
where c.iata_code = a.cty_iata_code;

The sql scripts of the 2 tables are as follows:

CREATE TABLE `my-database`.`cities` (
`IATA_CODE` varchar(3) default NULL,
`COU_IATA_CODE` varchar(2) default NULL,
`NAME` varchar(40) default NULL
) ENGINE=InnoDB DEFAULT CHT=latin1;


CREATE TABLE `my-database`.`airports` (
`IATA_CODE` varchar(3) default NULL,
`NAME` varchar(30) default NULL,
`CTY_IATA_CODE` varchar(3) default NULL,
`UTC_OFFSET` decimal(2,0) default NULL,
`ICAO_CODE` varchar(4) default NULL,
`ROUTE_SWARE_APT_NME` varchar(100) default NULL
) ENGINE=InnoDB DEFAULT CHT=latin1;

I would be really thankful for help with this query.

Thanks
ros

[strawberry]

On Apr 14, 11:56 am, "ros" <com> wrote: 

Try:

SELECT c.name,count(a.*) AS no_of_airports
FROM airports a
LEFT JOIN cities c ON c.IATA_CODE = a.CTY_IATA_CODE
GROUP BY a.CTY_IATA_CODE

[ros]

On Apr 14, 4:04 am, "strawberry" <com> wrote: 
> 
> 
> 
> 
> 
> 
> 
>
> Try:
>
> SELECT c.name,count(a.*) AS no_of_airports
> FROM airports a
> LEFT JOIN cities c ON c.IATA_CODE = a.CTY_IATA_CODE
> GROUP BY a.CTY_IATA_CODE[/ref]

Thanks for the reply Strawberry but I get the following error when I
run this command:

You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use
near '*) AS no_of_airports
FROM airports a
LEFT JOIN cities c ON c.IATA_CODE = a.CTY' at line 1

[strawberry]

On Apr 14, 12:10 pm, "ros" <com> wrote: 
> [/ref]
> [/ref]
> [/ref]
> [/ref]
> [/ref]
> [/ref]
> [/ref]
> [/ref]
> 
> 
>
> Thanks for the reply Strawberry but I get the following error when I
> run this command:
>
> You have an error in your SQL syntax; check the manual that
> corresponds to your MySQL server version for the right syntax to use
> near '*) AS no_of_airports
> FROM airports a
> LEFT JOIN cities c ON c.IATA_CODE = a.CTY' at line 1[/ref]

just remove a.

[ros]

On Apr 14, 4:50 am, "strawberry" <com> wrote: 
> [/ref]
> [/ref]
> [/ref]
> [/ref]
> [/ref]
> [/ref]
> [/ref]
> [/ref]
> [/ref]
> [/ref]
> [/ref]
> 
> 
>
> just remove a.[/ref]

Thanks again for the reply Strawberry.
I tried what you suggested but when I run the query without the a and
just (*) it gives me all cities with the no. of airports in each one.
While I want the city with the highest no. of airports.
Can you see what the problem is?
Thanks
ros

[subtenante]

On 15 Apr 2007 01:27:55 -0700, "ros" <com> wrote:
 

Add :

ORDER BY no_of_airports DESC
LIMIT 1;

at the end.

[ros]

On Apr 15, 1:54 am, subtenante <com> wrote: 
>
> Add :
>
> ORDER BY no_of_airports DESC
> LIMIT 1;
>
> at the end.[/ref]

Thanks a lot Sub. That solved the puzzle!
Cheers
ros