specifying multiple distinct columns in a COUNT() function.
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: specifying multiple distinct columns in a COUNT() function.

Hybrid View

  1. #1
    Join Date
    Sep 2000
    Posts
    362

    Arrow

    Hi, How do I specify multiple distinct columns in a count function. I am getting an error if I give

    SQL> select count(distinct company,time,region) from test;
    select count(distinct company,time,region) from test
    *
    ERROR at line 1:
    ORA-00909: invalid number of arguments

    But it works if I specify only one column

    1* select count(distinct (company)) from test
    SQL> /

    COUNT(DISTINCT(COMPANY))
    ------------------------
    478

    Please suggest.

    Thanks
    Anurag

  2. #2
    Join Date
    Feb 2001
    Posts
    163
    you can try

    select count(distinct company||time||region) from test

    See if that works

    uday

  3. #3
    Join Date
    Sep 2000
    Posts
    362
    It worked Uday, Thanks.

    But How and Why???????????

    Anurag

  4. #4
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    because instead of grouping one column you group a group of columns and make it a single column

    for example

    colA----------------------------colB
    1-------------------------------1
    1-------------------------------1
    1-------------------------------2
    2-------------------------------2
    3-------------------------------1
    3-------------------------------2
    2-------------------------------2

    if you use the query uday mentioned you are actually looking the distinct value

    11
    12
    22
    21
    32

    hm itīs a bit hard for me to explain, hope you understand it (this is like mathematics...)

  5. #5
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Or, you can try
    select count(*) from (select distinct company, time, region from test) t
    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."

  6. #6
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by marist89
    Or, you can try
    select count(*) from (select distinct company, time, region from test) t
    YES. Much more elegant solution. Although there is one more sort operation here, there is no implicit datatype conversion which might result in better performance.

    Also there is one more catch with uday's suggestion, which one must be avare of. TIME is obviously a DATE datatype. As there is no explicit conversion in uday's suggestion, Oracle performs implicit TO_CHAR conversion on date column. As default NLS_DATE_FORMAT *typicaly* contains only the date portion without the time component, the implicit date-to-char conversion will probably truncate the time portion, so you might actually get much less distinct rows as there realy are with "concatenation approach". If you go with this "distrinct concatenation" you'd better use explicit TO_CHAR date conversion with appropriate format mask.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  7. #7
    Join Date
    Sep 2000
    Posts
    362
    Hi,
    In my case time column has a varchar. ALso I did a test for performance by using "set timing on" and found that uday's solution is faster than marist89's.

    Otherwise marist89's solution is a better one if time column is of date datatype.

    Thanks
    Anurag

  8. #8
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Originally posted by jmodic
    YES. Much more elegant solution. Although there is one more sort operation here, there is no implicit datatype conversion which might result in better performance.
    I vote for COOL over FAST & EFFICIENT every time.
    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."

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