aggregate function problem
This is a discussion on aggregate function problem within the MySQL Database forums, part of the Database Forums category; Hi, I have 2 tables ( cities and airports) in a database and I want to run a query that gives ...
|
|||||||
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read |
04-14-2007
|
|||
|
|||
aggregate function problem
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 CHARSET=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 CHARSET=latin1; I would be really thankful for help with this query. Thanks ros 
|
|
04-14-2007
|
|||
|
|||
|
Re: aggregate function problem
On Apr 14, 11:56 am, "ros" <ros...@gmail.com> wrote:
> 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 CHARSET=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 CHARSET=latin1; > > I would be really thankful for help with this query. > > Thanks > ros 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
|
|
04-14-2007
|
|||
|
|||
|
Re: aggregate function problem
On Apr 14, 4:04 am, "strawberry" <zac.ca...@gmail.com> wrote:
> On Apr 14, 11:56 am, "ros" <ros...@gmail.com> wrote: > > > > > 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 CHARSET=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 CHARSET=latin1; > > > I would be really thankful for help with this query. > > > Thanks > > ros > > 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 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
|
|
04-14-2007
|
|||
|
|||
|
Re: aggregate function problem
On Apr 14, 12:10 pm, "ros" <ros...@gmail.com> wrote:
> On Apr 14, 4:04 am, "strawberry" <zac.ca...@gmail.com> wrote: > > > > > On Apr 14, 11:56 am, "ros" <ros...@gmail.com> wrote: > > > > 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 CHARSET=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 CHARSET=latin1; > > > > I would be really thankful for help with this query. > > > > Thanks > > > ros > > > 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 > > 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 just remove a.
|
|
04-15-2007
|
|||
|
|||
|
Re: aggregate function problem
On Apr 14, 4:50 am, "strawberry" <zac.ca...@gmail.com> wrote:
> On Apr 14, 12:10 pm, "ros" <ros...@gmail.com> wrote: > > > > > On Apr 14, 4:04 am, "strawberry" <zac.ca...@gmail.com> wrote: > > > > On Apr 14, 11:56 am, "ros" <ros...@gmail.com> wrote: > > > > > 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 CHARSET=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 CHARSET=latin1; > > > > > I would be really thankful for help with this query. > > > > > Thanks > > > > ros > > > > 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 > > > 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 > > just remove a. 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
|
|
04-15-2007
|
|||
|
|||
|
Re: aggregate function problem
On 15 Apr 2007 01:27:55 -0700, "ros" <ros060@gmail.com> wrote:
>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 Add : ORDER BY no_of_airports DESC LIMIT 1; at the end.
|
|
04-16-2007
|
|||
|
|||
|
Re: aggregate function problem
On Apr 15, 1:54 am, subtenante <zzsubtenant...@gmail.com> wrote:
> On 15 Apr 2007 01:27:55 -0700, "ros" <ros...@gmail.com> wrote: > > >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 > > Add : > > ORDER BY no_of_airports DESC > LIMIT 1; > > at the end. Thanks a lot Sub. That solved the puzzle! Cheers ros
|
Linear Mode
