Results 1 to 5 of 5

Thread: count(distinct)

  1. #1
    Join Date
    Jul 2003
    Posts
    1

    Unanswered: count(distinct)

    Hi,
    I was trying to run the following query on DB2 UDB AIX EEE environment, But I couldn't. could some tell me what is wrong with my sql...

    select count(distinct col1,col2,col3) from tab1

    I was able to run the following queries successfully.
    select coun(distinct col1) from tab1.
    select distinct col1,col2,col3 from tab1;

    Thanks,

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1

    Re: count(distinct)

    Originally posted by sunkarakk

    select count(distinct col1,col2,col3) from tab1

    I think you can only have one column referenced in the COUNT() function.

    You probably need this:

    with (select distinct col1,col2,col3 from tab1) as tmp1 select count(*) from tmp1;

    Nick

  3. #3
    Join Date
    Jul 2003
    Posts
    1

    Smile Re: count(distinct)

    Originally posted by sunkarakk
    Hi,
    I was trying to run the following query on DB2 UDB AIX EEE environment, But I couldn't. could some tell me what is wrong with my sql...

    select count(distinct col1,col2,col3) from tab1

    I was able to run the following queries successfully.
    select coun(distinct col1) from tab1.
    select distinct col1,col2,col3 from tab1;

    Thanks,

    i think you want is
    select count(*) from tab1
    group by col1,col2,col3
    it can retrieve the col1,col2,col3 combination number

  4. #4
    Join Date
    Nov 2002
    Posts
    14

    Re: count(distinct)

    Originally posted by sunkarakk
    Hi,
    I was trying to run the following query on DB2 UDB AIX EEE environment, But I couldn't. could some tell me what is wrong with my sql...

    select count(distinct col1,col2,col3) from tab1

    I was able to run the following queries successfully.
    select coun(distinct col1) from tab1.
    select distinct col1,col2,col3 from tab1;

    Thanks,
    You could also try the following:

    select count(distinct col1 || col2 || col3) from tab1

    assuming all columns are char or varchar; you'll need to apply CHAR functions to any non-char columns, e.g. if col2 and col3 are INTEGER:

    select count(distinct col1 || CHAR(col2) || CHAR(col3)) from tab1

  5. #5
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Just in case you have a composite index already created on the three colums (all in one index) you could make sure runstats is current and then select FULLKEYCARD from SYSCAT.INDEXES.

    There is also a column called FIRST3KEYCARD in case the index has more than 3 columns, but the distinct column count you wanted was the first 3 columns.

Posting Permissions

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