Rajesh Kumar Rajesh Kumar - 4 months ago 11
SQL Question

How to group by rather for date time?

Let's consider I am having following data in my table:

User id date time
User1. 28-JUL-16 06:14:56
User1. 28-JUL-16 04:12:12
User2. 28-JUL -16 05:10:45
User3. 30-JUL-16 03:10:12


I want to find the count of users on date wise.
Please some help on this!

Answer

You can ignore the time portion of a date using the trunc() function, which defaults to converting the time part to midnight. You can then group by that.

with your_table (user_id, date_time) as (
  select 'User1.', to_date('28-JUL-16 06:14:56', 'DD-MON-RR HH24:MI:SS') from dual
  union all select 'User1.', to_date('28-JUL-16 04:12:12', 'DD-MON-RR HH24:MI:SS') from dual
  union all select 'User2.', to_date('28-JUL-16 05:10:45', 'DD-MON-RR HH24:MI:SS') from dual
  union all select 'User3.', to_date('30-JUL-16 03:10:12', 'DD-MON-RR HH24:MI:SS') from dual
)
select to_char(trunc(date_time), 'DD-MON-RR') as date_only, count(distinct user_id)
from your_table
group by trunc(date_time);

DATE_ONLY                           COUNT(DISTINCTUSER_ID)
------------------ ---------------------------------------
28-JUL-16                                                2
30-JUL-16                                                1

I've included distinct as it isn't clear if you want to count a user more than once n the same day; if you do then just remove that.

select to_char(trunc(date_time), 'DD-MON-RR') as date_only, count(user_id)
from your_table
group by trunc(date_time);

DATE_ONLY                                   COUNT(USER_ID)
------------------ ---------------------------------------
28-JUL-16                                                3
30-JUL-16                                                1