CODEWITHSUNDEEP
sqlgroup-by
user1639485
user1639485

Reputation: 808

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!

Upvotes: 2

Views: 3934

Answers (2)

Gregory Bologna
Gregory Bologna

Reputation: 284

Apply your date format to group by as well.

select count(1) aa, to_char(somedate, 'DD-MON-YY') bb
    from sometable
    group by to_char(somedate, 'DD-MON-YY')
    order by 2 asc;

Upvotes: 1

user330315
user330315

Reputation:

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)

Upvotes: 9

Related Questions