Re: SQL counting to see trend

[Quoted] In article <3bb99812.8998852_at_news.easynet.co.uk>, library.treasures_at_NOSPAMsaqnet.co.uk says...
>
>There in an event LOG table, EVENTS are listed beside a TIME column.
>Each event corresponds to the point of time, accurately to the second,
>in which it took place.
>The table contains precisely three weeks of continuous log.
>
>I would like to learn how to query with the SQL count(*) statement for
>the following needs:
>
>(1) In order to see a coarse event density trend for an average day, I
>would like to count the events for each of the distinct twentyfour
>hours of an average day.
>

select count(*), trunc(TIME)
  from t
 <where .... if you want to>
 group by trunc(TIME);

>(2) Desirably, to sharpen the resolution and so to refine my analysis,
>I would like to reduce the hourly intervals to a few minutes, say,
>sufficiently enough, 5 minutes.
>

select count(*),

       trunc( time, 'hh' ) + trunc(to_char(created,'mi')/5)*5*(1/24/60)   from t
/

(change 5 in the above to 10 to get 10 minute intervals and so on -- change BOTH 5's

>(3) My next desired trend analysis would be to do the same for each
>distinct day of the week, i.e. from Monday to Sunday.
>

how is that different from #1?

>(4) Finally, I would like to create another coarse, general daily
>count, for the average week. This would obviously return just seven
>rows, one for each day of the week, from Monday to Sunday.
>

add where trunc(time) > trunc(sysdate-7) to #1 to get just that last week.

>Any kind help would be greatly appreciated.
>
>Thomas
>
>
>
>

--
Thomas Kyte (tkyte_at_us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp