Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2006
    Posts
    17

    Question Unanswered: select count (*) distinct on multiple cols

    Hi everybody. (MVS / platinum / spufi / db2v7)
    I have a query to ask you
    My table named TABLE has these colums in the index
    - TABLE_CLIENT_NAME
    - TABLE_CLIENT_CARS
    - TABLE_CLIENT_BILL_NUMBER
    For example we can have
    DUDE / PORSCHE / 001
    DUDE / PORSCHE / 002
    DUDE / PORSCHE / 003
    DUDE / FERRARI / 001
    DUDE / MASERATI /001
    MIKE / BUICK / 001
    MIKE / BUICK / 002
    PAM / CORVETTE / 001
    I want to know the number of rows of this TABLE
    GROUP BY TABLE_CLIENT_NAME and TABLE_CLIENT_CAR
    The query should gave me 5
    I try to make select count(*) from (select distinct (table_client_name, table_client_cars) from TABLE)
    Thanks for your reply

  2. #2
    Join Date
    Dec 2005
    Posts
    273
    This should work:

    select count(*) from
    (select distinct table_client_name, table_client_cars from TABLE) A

  3. #3
    Join Date
    Mar 2006
    Posts
    17
    It works fine !!
    Thank you so much.

    I don't understand why should we type the "A" at the end of the query.

  4. #4
    Join Date
    Dec 2005
    Posts
    273
    If you use a nested table expression, you have to add a correlation name.
    It doesn't matter what the name is ( "A" or "HUGO" or "THISISANAME" or whatever you want ) but you have to add it for syntax reasons.

  5. #5
    Join Date
    Mar 2006
    Posts
    17
    Thanks for th explanation
    Cheers

Posting Permissions

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