Professional Web Applications Themes

aggregate function problem - MySQL

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, ...

  1. #1

    Default 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 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

    ros Guest

  2. #2

    Default Re: aggregate function problem

    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

    strawberry Guest

  3. #3

    Default Re: aggregate function problem

    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

    ros Guest

  4. #4

    Default Re: aggregate function problem

    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.

    strawberry Guest

  5. #5

    Default Re: aggregate function problem

    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

    ros Guest

  6. #6

    Default Re: aggregate function problem

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

    Add :

    ORDER BY no_of_airports DESC
    LIMIT 1;

    at the end.
    subtenante Guest

  7. #7

    Default Re: aggregate function problem

    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

    ros Guest

Similar Threads

  1. top three aggregate sum function
    By Geoff in forum MySQL
    Replies: 8
    Last Post: April 12th, 02:29 PM
  2. Replies: 7
    Last Post: April 6th, 05:47 PM
  3. Aggregate Function Nightmare
    By Henweigh99 in forum Macromedia ColdFusion
    Replies: 15
    Last Post: July 14th, 02:54 AM
  4. Aggregate Bitwise OR Function
    By Mark in forum IBM DB2
    Replies: 3
    Last Post: September 25th, 06:53 PM
  5. Replies: 3
    Last Post: July 4th, 01:53 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139