counts, grouped by the hour 1

[fidge]

Hi,

I have a table that holds data for the whole days transactions. Each record has a date/time entry.

i.e trans1,user,date/time

I would like to count how many transaction i have received per hour. Could somebody explain the correct way to do this please.

Thanks in advance for any help
fidge.

 

[SantaMufasa]

Fidge,

Here is a solution you can use:

Section 1 -- Sample data:

select trans1
      ,usr
      ,to_char(date_time,'dd-MON-yyyy hh24:mi:ss') date_time
  from fidge;

    TRANS1        USR DATE_TIME
---------- ---------- --------------------
        10          1 27-JUN-2006 04:33:02
        20          2 27-JUN-2006 04:13:02
        30          3 27-JUN-2006 03:53:02
        40          4 27-JUN-2006 03:33:02
        50          5 27-JUN-2006 03:13:02
        60          6 27-JUN-2006 02:53:02
        70          7 27-JUN-2006 02:33:02
        80          8 27-JUN-2006 02:13:02
        90          9 27-JUN-2006 01:53:02
       100         10 27-JUN-2006 01:33:02
       110         11 27-JUN-2006 01:13:02
       120         12 27-JUN-2006 00:53:02
       130         13 27-JUN-2006 00:33:02
       140         14 27-JUN-2006 00:13:02
       150         15 26-JUN-2006 23:53:02
       160         16 26-JUN-2006 23:33:02
       170         17 26-JUN-2006 23:13:02
       180         18 26-JUN-2006 22:53:02
       190         19 26-JUN-2006 22:33:02
       200         20 26-JUN-2006 22:13:02
       210         21 26-JUN-2006 21:53:02
       220         22 26-JUN-2006 21:33:02

22 rows selected.

Section 2 -- Grouping report by hour of day:

select count(*),dt,hr
  from (select to_char(date_time,'dd-mm-yyyy') dt
              ,to_char(date_time,'hh24')HR
          from fidge)
 group by dt,HR;

  COUNT(*) DT         HR
---------- ---------- --
         2 26-06-2006 21
         3 26-06-2006 22
         3 26-06-2006 23
         3 27-06-2006 00
         3 27-06-2006 01
         3 27-06-2006 02
         3 27-06-2006 03
         2 27-06-2006 04

8 rows selected.

Let us know if this does what you wanted.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[fidge]

thanks santamufasa that worked perfectly.