Oracle SQL - how to group and aggregate results?

Question

I am trying to query an audit table in Oracle to pull out a list of how popular a document is during a given timeframe (number of unique hits).

The SQL that I've developed so far is:

select distinct dt.name "Document name", ku.name "User name", count(*) "Number of hits" 
  from dauditnew da, 
       kuaf ku, 
       dtree dt 
 where ku.id=da.performerid 
   and (da.auditstr='Fetch') 
   and dt.dataid = ANY(DOCID1,DOCID2) 
   and da.dataid = ANY(DOCID1,DOCID2) 
   and da.auditdate between TO_DATE('01-JAN-12') and TO_DATE('31-OCT-12') 
 group by dt.name, ku.name

And it spits out data similar to:

Document name       User name           Hit count
document1.pdf   rimnet\user1    1
document1.pdf   rimnet\user2    1
document1.pdf   rimnet\user3    17
document1.pdf   rimnet\user4    1
document2.pdf   rimnet\user5    1
document1.pdf   rimnet\user6    1
document3.pdf   rimnet\user7    1

But what I really need is for it to group the results and count each unique user once:

Document name       User name           Hit count
document1.pdf       n/a                 5
document2.pdf       n/a                 1
document3.pdf       n/a                 1

Can anyone point me in the right direction?

Answer 1

Based on the results you want, it sounds like you just want a count of the users rather than a count of the hits (otherwise, it doesn't make sense to aggregate 17 hits + 1 hit + 1 hit + 1 hit + 1 hit and end up with 5 hits). If that's the case

select dt.name "Document name", count(distinct ku.name) "Distinct users" 
  from dauditnew da, 
       kuaf ku, 
       dtree dt 
 where ku.id=da.performerid 
   and (da.auditstr='Fetch') 
   and dt.dataid = ANY(DOCID1,DOCID2) 
   and da.dataid = ANY(DOCID1,DOCID2) 
   and da.auditdate between TO_DATE('01-JAN-12') and TO_DATE('31-OCT-12') 
 group by dt.name