3 Replies Latest reply: Nov 25, 2009 9:35 AM by Arunkumar Ramamoorthy-Oracle RSS

    COUNT(DISTINCT) on multiple columns?

    Donbot
      Is there an easier way of doing a COUNT(DISTINCT...) on multiple items than converting them to strings and concatenating them?

      i.e. if I have a table with column string1 as VARCHAR2(1000), number2 as NUMBER, and date3 as DATE, and I want a count on how many distinct combinations of the three exist, is there a better way than:
      SELECT COUNT(DISTINCT string1 || TO_CHAR(number2) || TO_CHAR(date3, 'YYYYMMDD'))
      -- Don
        • 1. Re: COUNT(DISTINCT) on multiple columns?
          730185
          select count(*) from
          (
              select distinct <list of columns> from table
          )
          • 2. Re: COUNT(DISTINCT) on multiple columns?
            Frank Kulash
            Hi,

            If you just want one row of output, with the total number of distinct combinations, you can use aggregate functions:
            SELECT    COUNT (COUNT (*))     AS total_combinations
            FROM       table_x
            GROUP BY  string1
            ,            number2
            ,       TRUNC (date3)          -- or just date3, depending on your data and requirements
            ;
            For example, the scott.emp table has 3 distinct deptnos and 5 distinct jobs, but, as, it happens, 9 distinct combinations of deptno and job.
            To see that:
            SELECT    COUNT (COUNT (*))     AS total_combinations
            FROM       scott.emp
            GROUP BY  deptno
            ,            job
            ;
            You can also use analytic functions.

            Edited by: Frank Kulash on Nov 25, 2009 10:34 AM
            • 3. Re: COUNT(DISTINCT) on multiple columns?
              Arunkumar Ramamoorthy-Oracle
              Hi,

              Why not a group by?
              SQL> ed
              Wrote file afiedt.buf
              
                1  with t as
                2  (
                3  select 'string1' string1, 1 number1, to_date('10-NOV-2009','DD-MON-YYYY') date1 from dual
                4  union all select 'string2',1,to_date('10-NOV-2009','DD-MON-YYYY') from dual
                5  union all select 'string1',1,to_date('11-NOV-2009','DD-MON-YYYY') from dual
                6  union all select 'string1',2,to_date('11-NOV-2009','DD-MON-YYYY') from dual
                7  union all select 'string2',1,to_date('10-NOV-2009','DD-MON-YYYY') from dual
                8  )
                9  select string1, number1, date1 from t
               10* group by string1, number1, date1
              SQL> /
              
              STRING1    NUMBER1 DATE1
              ------- ---------- ---------
              string1          1 11-NOV-09
              string2          1 10-NOV-09
              string1          1 10-NOV-09
              string1          2 11-NOV-09
              
              SQL> ed
              Wrote file afiedt.buf
              
                1  with t as
                2  (
                3  select 'string1' string1, 1 number1, to_date('10-NOV-2009','DD-MON-YYYY') date1 from dual
                4  union all select 'string2',1,to_date('10-NOV-2009','DD-MON-YYYY') from dual
                5  union all select 'string1',1,to_date('11-NOV-2009','DD-MON-YYYY') from dual
                6  union all select 'string1',2,to_date('11-NOV-2009','DD-MON-YYYY') from dual
                7  union all select 'string2',1,to_date('10-NOV-2009','DD-MON-YYYY') from dual
                8  )
                9  select string1, number1, date1 from t
               10  group by string1, number1, date1
               11* having count(*) > 1
              SQL> /
              
              STRING1    NUMBER1 DATE1
              ------- ---------- ---------
              string2          1 10-NOV-09
              -Arun