Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2004
    Posts
    5

    Unhappy Unanswered: Count from 2 tables

    I have created a two tables and inserted a few data into those tables... but i'm trying to list out all the pilots and the number of types of airplane each is able to fly with the pilot's name and license number. I'm not quite sure how to do it. Can someone help me out?

    Table: pilot
    person_nbr license_nbr last_name first_name
    1001 5033 Hui A
    1002 4848 Chan C
    1003 3214 Smith B
    1004 1423 Wilson D

    Table: files
    license_nbr plane_type
    5033 single engine
    4848 dual engine
    4848 single engine
    1423 single engine
    1423 dual engine
    4848 turbo props

    I tried using
    SELECT license_nbr, COUNT(DISTINCT plane_type)
    FROM files
    GROUP BY license_nbr
    the result is what i wanted but i don't know how i can get the pilot's last name and first name in there as well.

  2. #2
    Join Date
    Jul 2003
    Posts
    73
    Do a join from the pilot to the files table:

    SELECT a.licence_nbr, a.last_name, a.first_name, COUNT(DISTINCT b.plane_type)
    FROM pilot a LEFT OUTER JOIN files b ON a.licence_nbr = b.licence_nbr
    GROUP BY a.licence_nbr

    This way you'll also get pilots that have 0 plane types
    Last edited by joeldixon66; 10-26-04 at 01:34.
    Joel Dixon
    Analyst Programmer
    Melbourne, Australia

  3. #3
    Join Date
    Oct 2004
    Posts
    5
    For some reason there is an error message that poops up saying

    SELECT a.license_nbr,a.last_name,a.first_name,COUNT(DISTI NCT b.plane_type)
    2 FROM pilot a, files b
    3 WHERE a.license_nbr=b.license_nbr;
    SELECT a.license_nbr,a.last_name,a.first_name,COUNT(DISTI NCT b.plane_type)
    *
    ERROR at line 1:
    ORA-00937: not a single-group group function

    it works without the COUNT command.. just with b.plane_type
    what should i do??

  4. #4
    Join Date
    Jul 2003
    Posts
    73
    You need to group by license_nbr

    (GROUP BY a.license_nbr) as the last line.
    Joel Dixon
    Analyst Programmer
    Melbourne, Australia

  5. #5
    Join Date
    Oct 2004
    Posts
    5
    This is what I get when I use GROUP By

    SELECT a.license_nbr,a.last_name,a.first_name,COUNT(DISTI NCT b.plane_type)

    FROM pilot a, files b
    WHERE a.license_nbr=b.license_nbr
    GROUP BY a.license_nbr;
    2 3 4 SELECT a.license_nbr,a.last_name,a.first_name,COUNT(DISTI NCT b.pl
    ane_type)
    *
    ERROR at line 1:
    ORA-00979: not a GROUP BY expression

    Did I do something wrong?

  6. #6
    Join Date
    Jul 2003
    Posts
    73
    You're not actually putting a space in DISTINCT are you?

    You've also got to group by all fields that are not part of an aggregate function:

    Code:
    SELECT a.license_nbr, a.last_name, a.first_name, COUNT(DISTINCT b.plane_type)
    FROM pilot a, files b
    WHERE a.license_nbr=b.license_nbr
    GROUP BY a.license_nbr, a.last_name, a.first_name;
    Joel Dixon
    Analyst Programmer
    Melbourne, Australia

  7. #7
    Join Date
    Oct 2004
    Posts
    5
    Thank you so much~ I finally understand what to do when joining tables. Thank you!

Posting Permissions

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