how to write a query to count the number of days that a user has logged in?

The site log records a datetime stamp each time a user logs in. I am in need of a query to count the number of days that a user has logged in. Just doing a count on the [DateTime] field doesn't work because a user can login more than once per day. So, I am stumped as how to just count distinct days that they logged in. Basically, we need the query to produce:

UserID
NumberOfDaysLoggedIn

For example, User John (ID 33) logins in as such:

3/26/2008 12:20 PM
3/26/2008 5:58 PM
3/27/2008开发者_如何学JAVA 11:46 AM

The results we want would be:

UserID    NumberofDaysLoggedIn
    33                       2

Any ideas to produce that results by using oracle query . please suggest any idea

---

what you should do is round the dates and then put them under distinct.

the function that round dates is trunc().

you can do it like that:

select count(*)
from (select name, trunc(date)
      from table
      group by name, trunc(date))
where name = 'John';

if you want to get the result for each user you could do it like that:

select name, count(*)
from (select distinct name, trunc(date)
      from table)
group by name;

---

you need to do something like;

select userID, count(distinct trunc(date))
from table

---

with t as 
(
select 1 as id, sysdate as d from dual
union all
select 1 as id, sysdate - 0.5 as d from dual
union all
select 1 as id, sysdate - 0.6 as d from dual
union all
select 1 as id, sysdate - 1 as d from dual
union all
select 2 as id, sysdate - 1 as d from dual
)
select id, count(distinct trunc(d))
from t 
group by id
;

---

You can count distinct dates, something along the lines of:

select count(distinct trunc(t.date))
  from table t
 where t.userId = some user id

trunc() truncates the date to the day value but can also take format parameter for truncating to a specific unit of measure.