How to construct this query in oracle?

Question

Got table ABC with one column. A date column "created". So sample values are like;

 created
 2009-06-18 13:56:00
 2009-06-18 12:56:00
 2009-06-17 14:02:00
 2009-06-17 13:12:23
 2009-06-16 10:02:10

I want to write a query so that the results are:

count    created
2        2009-06-18
2        2009-06-17
1        2009-06-16

Basically count how many entries belong to each date, but ignoring time.

This is in PL-SQL with Oracle.

Any ideas?

Answer 1

The TRUNC function returns the DATE of the DATETIME.

select trunc(created),count(*) from ABC group by trunc(created)

Answer 2

select count(*), to_char('YYYY-MM-DD', created) from ABC group by to_char('YYYY-MM-DD', created)

Answer 3

Just for completeness, I'll add a generalized variant that works for arbitrarily defined bucket sizes:

  SELECT trunc( (created - to_date('2000.01.01', 'YYYY.MM.DD'))
                * 24
              ) / 24
         + to_date('2000.01.01', 'YYYY.MM.DD') theDate
       , count(*)
    FROM Layer
GROUP BY trunc( (created - to_date('2000.01.01', 'YYYY.MM.DD'))
                * 24
              ) / 24
         + to_date('2000.01.01', 'YYYY.MM.DD')
ORDER BY 1;

The query above will give you the counts by hour; use something smaller than 24 to get larger intervals, or bigger to create smaller intervals. By reversing the position of the * and /, you can make your buckets be in increments of days (e.g. doing " / 7 " instead of " * 24 " would give you buckets of one week each.