Search code examples
sqlgroup-by

Group by <a date field> is not working in my Sql (Oracle)

I want to count the number of registrations done on each day of a given year(say, the reg table has first_name,last_name and reg_date as the columns).

This is what I am trying:

select reg_date, count(reg_date) 
from reg 
where reg_date >= to_date('01-JAN-12', 'DD-MON-YY') 
group by (reg_date)

The results are not as expected. It is not grouping by the reg_date and showing the registration date for each registration.

My reg_date has DATE type.

Thank you!

Solution

  • This is most probably because of the time part that every DATE carries. To get rid of it use the trunc function:

    select trunc(reg_date), count(trunc(reg_date))
from reg 
where reg_date >= to_date('01-JAN-12', 'DD-MON-YY') 
group by trunc(reg_date)