This discussion is archived
4 Replies Latest reply: Dec 4, 2012 12:41 AM by theoa RSS

distinct count on multiple columns

949210 Newbie
Currently Being Moderated
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 Expert
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points