SQL/Oracle Grouping Data by field by hours for a certain type on a given day -

okay trying construct single query save myself whole bunch of time (rather writing ton of seperate queries), don't know how start on this.

what need @ single day , type , break out counts on actions, hour, between 8:00am - 8:00pm. example have following fake table

type_   action_     timestamp_ ------------------------------       processed   2010-11-19 10:00:00.000       processed   2010-11-19 10:46:45.000       processed   2010-11-19 11:46:45.000       processed   2010-11-19 12:46:45.000       processed   2010-11-19 12:48:45.000       pending     2010-11-19 11:46:45.000       pending     2010-11-19 11:50:45.000       pending     2010-11-19 12:46:45.000       pending     2010-11-19 12:48:45.000 b       pending     2010-11-19 19:48:45.000 b       pending     2010-11-19 21:46:45.000 .etc 

so if wanted @ records with

• type_ = 'a'
• on date 2010-11-19
• grouped action_ per hour

i see result

action_ numoccurences   range --------------------------------------------- processed  2                10:00:00 - 11:00:00 pending    0                10:00:00 - 11:00:00 processed  1                11:00:00 - 12:00:00 pending    2                11:00:00 - 12:00:00 processed  2                12:00:00 - 13:00:00 pending    2                12:00:00 - 13:00:00 

or similar that, should @ least give idea of looking for.

can help? try provide sample code i'm working with, have no idea how work group clauses needed make happen.

select    action_,    count(*) numoccurences,    to_char(timestamp_       , 'hh24') || ':00:00-' ||     to_char(timestamp_ + 1/24, 'hh24') || ':00:00' range      tq84_action        timestamp_ between timestamp '2010-11-19 08:00:00' ,                        timestamp '2010-11-19 20:00:00' ,    type_ = 'a'  group    action_,    to_char(timestamp_       , 'hh24') || ':00:00-' ||     to_char(timestamp_ + 1/24, 'hh24') || ':00:00'   order     range; 

now, above select statement returns hours in there @ least on action. in order show record hour - {processed/pending} combinations, following amendments should made query:

select    action_,    count(type_) numoccurences,    to_char(timestamp_       , 'hh24') || ':00:00-' ||     to_char(timestamp_ + 1/24, 'hh24') || ':00:00' range_  (    select * tq84_action              timestamp_ between timestamp '2010-11-19 08:00:00' ,                           timestamp '2010-11-19 20:00:00' ,       type_ = 'a'     union (       select          null type_,         action.name_ action_,         date '2010-11-19' + 8/24 + hour.counter_ / 24 timestamp_1       (         select             level-1 counter_         dual           connect level <= 12       ) hour,       (          select 'processed' name_ dual union         select 'pending'   name_ dual       ) action     )  )  group    action_,    to_char(timestamp_       , 'hh24') || ':00:00-' ||     to_char(timestamp_ + 1/24, 'hh24') || ':00:00'   order     range_; 

btw, here's ddl , dml used:

drop   table tq84_action; create table tq84_action (   type_      varchar2( 1),   action_    varchar2(10),   timestamp_ timestamp );   insert tq84_action values('a', 'processed' , timestamp '2010-11-19 10:00:00.000'); insert tq84_action values('a', 'processed' , timestamp '2010-11-19 10:46:45.000'); insert tq84_action values('a', 'processed' , timestamp '2010-11-19 11:46:45.000'); insert tq84_action values('a', 'processed' , timestamp '2010-11-19 12:46:45.000'); insert tq84_action values('a', 'processed' , timestamp '2010-11-19 12:48:45.000'); insert tq84_action values('a', 'pending'   , timestamp '2010-11-19 11:46:45.000'); insert tq84_action values('a', 'pending'   , timestamp '2010-11-19 11:50:45.000'); insert tq84_action values('a', 'pending'   , timestamp '2010-11-19 12:46:45.000'); insert tq84_action values('a', 'pending'   , timestamp '2010-11-19 12:48:45.000'); insert tq84_action values('b', 'pending'   , timestamp '2010-11-19 19:48:45.000'); insert tq84_action values('b', 'pending'   , timestamp '2010-11-19 21:46:45.000');