how to select count(distinct)?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: how to select count(distinct)?

  1. #1
    Join Date
    Jun 2001
    Posts
    193
    select count(distinct a) from tableA
    works fine.

    but select count(distinct a,b) from tableA;
    didn't work.

    why?
    guru is on the way!!!!

  2. #2
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    select count(distinct a||b) from tableA

    distinct is for 1 column not for 2, by concatenating the two columns it becomes one so distinct works

  3. #3
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    It doesn't work because count() operates on one field. Here, you are specifying two fields. SELECT count(a,b) doesn't work either, so why should SELECT count(distinct a, b) work?

    Maybe you are looking for SELECT count(distinct a), count(distinct b) ?
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  4. #4
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    I make a pardon, count is for 1 column not distinct


    select count(distinct a||rpad(' ', 10, ' ')||b) from tableA

    probably give you better reading


  5. #5
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Or maybe

    SELECT COUNT(*) FROM (SELECT DISTINCT a, b FROM tableA);

    which aslo takes care of possible quirks if a and b are not of character type....

    [Edited by jmodic on 10-03-2001 at 04:38 PM]
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  6. #6
    Join Date
    Jul 2000
    Posts
    521
    Your table XYZ. Two column I and J. Any datatype.

    Define a view as follows :
    create view vw_xyz as
    select i,j from xyz
    union
    select i,j from xyz;

    Then select count(*) from vw_xyz gives you the intended result of count(distinct i,j).
    svk

  7. #7
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by svk
    Your table XYZ. Two column I and J. Any datatype.

    Define a view as follows :
    create view vw_xyz as
    select i,j from xyz
    union
    select i,j from xyz;

    Then select count(*) from vw_xyz gives you the intended result of count(distinct i,j).
    Yes, but at what expences? Two full table scans! Your view would retur exactly the same result if you change it to more simple and much more efficient query:

    SELECT DISTINCT i, j FROM xyz;
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

Posting Permissions

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



Click Here to Expand Forum to Full Width