4 Replies Latest reply: Dec 4, 2012 11:08 PM by 949210 RSS

    distinct count on multiple columns

    949210
      Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
      PL/SQL Release 10.2.0.1.0 - Production
      "CORE     10.2.0.1.0     Production"
      TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
      NLSRTL Version 10.2.0.1.0 - Production
      select  distinct USERID,REG_DATE,COUNT(*) CNT from REGISTRATION where TRUNC(REG_DATE)>TO_DATE('01-NOVEMBER-2012','DD-MONTH-YYYY')
      and REG_ID not in (select REG_ID from REG_SEG_CAT) group by USERID,REG_DATE   
      )
      41     27-NOV-12     1
      43     27-NOV-12     1
      43     27-NOV-12     1
      any idea how to show count=2 for userid 43

      Edited by: 946207 on Dec 4, 2012 2:11 PM
        • 1. Re: SQL
          AlbertoFaenza
          Hi,

          How could we know if you don't show us your input data?

          Please read SQL and PL/SQL FAQ

          Additionally when you put some code please enclose it between two lines starting with {noformat}
          {noformat}
          i.e.:
          {noformat}
          {noformat}
          SELECT ...
          {noformat}
          {noformat}
          
          Regards.
          Al                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
          • 2. Re: distinct count on multiple columns
            theoa
            Try this (untested, since I don't have your table or data).
            the distinct is not necessary here because of the group by.
            select USERID
            ,      trunc(REG_DATE)
            ,      COUNT(*) CNT
            from   REGISTRATION
            where  TRUNC(REG_DATE)>TO_DATE('01-NOVEMBER-2012','DD-MONTH-YYYY')
            and    REG_ID not in (select REG_ID
                                  from REG_SEG_CAT
                                 )
            group by USERID
            ,     trunc(REG_DATE)
            • 3. Re: distinct count on multiple columns
              Purvesh K
              946207 wrote:
              Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
              PL/SQL Release 10.2.0.1.0 - Production
              "CORE     10.2.0.1.0     Production"
              TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
              NLSRTL Version 10.2.0.1.0 - Production


              select distinct USERID,REG_DATE,COUNT(*) CNT from REGISTRATION where TRUNC(REG_DATE)>TO_DATE('01-NOVEMBER-2012','DD-MONTH-YYYY')
              and REG_ID not in (select REG_ID from REG_SEG_CAT) group by USERID,REG_DATE
              )
              41     27-NOV-12     1
              43     27-NOV-12     1
              43     27-NOV-12     1
              any idea how to show count=2 for userid 43
              Distinct isn't working probably because the REG_DATE has some time component attached that is different for Instance 1 of user_id 43 from Instance 2.

              Perhaps
              select  distinct USERID,TRUNC(REG_DATE),COUNT(*) CNT from REGISTRATION where TRUNC(REG_DATE)>TO_DATE('01-NOVEMBER-2012','DD-MONTH-YYYY')
              and REG_ID not in (select REG_ID from REG_SEG_CAT) group by USERID,REG_DATE)
              should give you desired result.

              If Not, then you can try this:
              select USERID, REG_DATE, CNT,
                from (
                         select  distinct USERID, REG_DATE, COUNT(*) CNT,
                                   row_number() over (order by reg_date) rn
                           from REGISTRATION 
                         where TRUNC(REG_DATE)>TO_DATE('01-NOVEMBER-2012','DD-MONTH-YYYY')
                            and REG_ID not in (select REG_ID from REG_SEG_CAT) group by USERID,REG_DATE)
                       )
               where rn = 1;
              Edited by: Purvesh K on Dec 4, 2012 2:12 PM
              • 4. Re: SQL
                949210
                4th december
                the userid 43 has no records in reg_seg_cat table so this query has given that as an output
                only count does not seem to count it
                for 43 count(*) should be showing 2

                5th december
                trunc has to be included in all the places where it is mentioned
                select  distinct USERID,TRUNC(REG_DATE),COUNT(*) CNT from REGISTRATION where TRUNC(REG_DATE)>TO_DATE('01-NOVEMBER-2012','DD-MONTH-YYYY')
                and REG_ID not in (select REG_ID from REG_SEG_CAT) group by USERID,trunc(REG_DATE)
                thanks people.. marking the thread as answered now
                :-)

                Edited by: 946207 on Dec 5, 2012 10:35 AM