Results 1 to 12 of 12

Thread: Query Question

  1. #1
    Join Date
    Sep 2004
    Location
    Virginia, USA
    Posts
    98

    Unanswered: Query Question

    My database has multiple serial numbers in it and each serial number is associated with multiple phone numbers. I'm trying to figure out a way to design a query that lists each serial number just once so I can get a count of the equipment. The phone number is not important or needed for the query. Thanks.

    kc

  2. #2
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    Select ta.serial, TB_VIEW.CNT
    from tableA ta
    INNER JOIN
    (
    Select tb.serial, count(*) as CNT
    from tableB tb
    where tb.phoneNumber IS NOT NULL
    group by tb.serial
    ) TB_VIEW ON
    ta.id = TB_VIEW.id
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  3. #3
    Join Date
    Sep 2004
    Location
    Virginia, USA
    Posts
    98
    Robert,

    Thanks for the help but confused on what it does and how to fill my fields/tables into the code.

    Table name is: TelephoneList
    Field Name: PhoneSerial
    Field Name: PhoneNumber

    Just need all unique serials listed with no duplicates. Each PhoneSerial can have man PhoneNumber associated with them.

  4. #4
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    Hi,

    1) List of serial numbers and the count of all associated telephone numbers.
    Select tl.phoneSerial, count(*)
    from telephoneList tl
    where PhoneNumber IS NOT NULL
    group by tl.phoneSerial

    2) List of unique serial numbers
    Select distinct tl.phoneSerial
    from telephoneList tl
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  5. #5
    Join Date
    Sep 2004
    Location
    Virginia, USA
    Posts
    98
    Thanks. I'll give it a shot.

    kc

  6. #6
    Join Date
    Sep 2004
    Location
    Virginia, USA
    Posts
    98
    Getting a syntax error.
    I'm trying to include multiple fields as well as the serialnumber field.
    Doesn't appear to recognize "distinct" but not sure.

  7. #7
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    Quote Originally Posted by kccpo
    I'm trying to include multiple fields as well as the serialnumber field.
    I assume your table is as follows:

    tableA
    ------

    serialA phone1
    serialA phone2
    serialB phone1
    ...

    Could you please further clarify the output that you require.

    Thanks.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  8. #8
    Join Date
    Sep 2004
    Location
    Virginia, USA
    Posts
    98
    Robert,
    Thanks for your patience.

    I'm looking for the following fields to be displayed from the table PhoneListing
    PhoneSerial
    PhoneOwner
    PhoneModel

    Table PhoneListing has
    Serial Number Owner Model
    PhoneA 12345 Jones Alpha
    PhoneA 23456 Jones Alpha
    PhoneB 34567 Smith Bravo
    PhoneB 45678 Smith Bravo

    Want the following Displayed:
    Serial Owner Model
    PhoneA Jones Alpha
    PhoneB Smith Bravo

    Thanks.

  9. #9
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    Ok,

    My understanding is that you want a set of unique combinations of Serial, Owner and Model.

    Select distinct pl.Serial, pl.Owner, pl.Model
    from phoneListing pl

    Alternatively, if you want to list the count of numbers for each of the above combinations, then:

    Select pl.serial, pl.owner, pl.model, count(*)
    from phoneListing pl
    group by pl.serial, pl.owner, pl.model

    You can simply omit the count(*) column to obtain the same result as the first query.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  10. #10
    Join Date
    Sep 2004
    Location
    Virginia, USA
    Posts
    98
    I trying to identify the unique serial numbers so as to get a count of phones. When I used your first set of code it is asking for user input for pl.unit & Command

    Not sure what to put in as I want a list of all different serial numbers.

    Sorry for being a pain. I guess I"m not stating the problem clearly.

  11. #11
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    Please find attached a copy of your sample data and the corresponding query.
    Attached Files Attached Files
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  12. #12
    Join Date
    Sep 2004
    Location
    Virginia, USA
    Posts
    98
    Robert,

    Thanks for all of the help. It is working just like I need it!

    Appreciate your patience and expertise.

    kc

Posting Permissions

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