Results 1 to 8 of 8
  1. #1
    Join Date
    Nov 2002
    Posts
    94

    Unanswered: count distinct rows in a table

    Hi,

    I want a count of distinct rows in a table through a single query -- is it possible?

    eg.

    table-

    create table ch1 (a int, b int, c int, d int)

    insert ch1 values (1,1,1,1)
    insert ch1 values (2,2,2,2)
    insert ch1 values (1,1,1,1)
    insert ch1 values (2,2,2,2)
    insert ch1 values (1,3,4,5)

    Here distinct row count in a table is 3 which I want to achieve thro a query.

    if I do

    select count(distinct a) from ch1 it works fine and gives me output as 2.

    but this is not working

    select count(distinct a,b,c,d) from ch1 - any workaround to find the distinct row count in a table??

    Please reply.

    Cheers!
    Ram.

  2. #2
    Join Date
    Nov 2002
    Posts
    94

    distinct row count in a table.

    Hi,

    I want a count of distinct rows in a table through a single query -- is it possible?

    eg.

    table-

    create table ch1 (a int, b int, c int, d int)

    insert ch1 values (1,1,1,1)
    insert ch1 values (2,2,2,2)
    insert ch1 values (1,1,1,1)
    insert ch1 values (2,2,2,2)
    insert ch1 values (1,3,4,5)

    Here distinct row count in a table is 3 which I want to achieve thro a query.

    if I do

    select count(distinct a) from ch1 it works fine and gives me output as 2.

    but this is not working

    select count(distinct a,b,c,d) from ch1 - any workaround to find the distinct row count in a table??

    Please reply.

    Cheers!
    Ram.

  3. #3
    Join Date
    Nov 2005
    Location
    San Francisco, CA
    Posts
    506
    Try this...

    SELECT COUNT(*)
    FROM
    (SELECT DISTINCT * FROM ch1)ch1
    Success is the ability to go from one failure to another with no loss of enthusiasm.
    - Sir Winston Churchill
    Joydeep

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Or
    Code:
    SELECT COUNT(DISTINCT *) AS Distinct_Rows FROM ch1
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1

    select sum(case when count(*)>1 then 1 else 1 end)
    from ch1 group by a,b,c,d

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    threads merged

    ramshree, please do not post the same question into multiple forums
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Aug 2006
    Posts
    1
    I have an example below: You should use the "having" clause.
    db2 "select serialno,count(*) from svcprd.bcbs_unix_sysinfo group by serialno having count(*)>1

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by techxan
    I have an example below: You should use the "having" clause.
    db2 "select serialno,count(*) from svcprd.bcbs_unix_sysinfo group by serialno having count(*)>1
    I think that you're "close, but no banana" on this... The code that you posted will actually count the non-distinct rows (how many rows have at least one duplicated row elsewhere).

    -PatP

Posting Permissions

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