Re: SQL counting to see trend

In article <3bba091a.37199510_at_news.easynet.co.uk>, library.treasures_at_NOSPAMsaqnet.co.uk says...
>
>On 2 Oct 2001 06:22:16 -0700, Thomas Kyte <tkyte_at_us.oracle.com> wrote:
>
>>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);
>>

ok, in light of your comments below -- it might be that this in fact the answer to #2 and #1 is a single row:

select avg(count(*))
  from t
 where ....
 group by trunc(time);

that'll get the avg count for you.

>>>(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?
>
>What I meant was doing the count specifically for Mondays, Tuesdays,
>and so on to Sundays, in seven separate counts. Thus, I would see how
>an average Wednesday is different from an average Sunday, for example.
>
>So, the distinct results for these would expectedly be different from
>that of #1.
>
>In #1, the mid-week and weekend patterns could not be juxtaposed, for
>they are averaged out in a single result for an average day.
>>
>>>(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.
>
>Thank you very much indeed, Tom.
>
>I am going to try your suggested queries, though I am not exactly sure
>yet what do you mean by "t"? Is it some special table, or rather
>simply the abbreviation for the applicable table?
>The table I use is called "LOG".
>

T just stands for "table" -- you use your tablename there...

>I am new to SQL but I like its promising statistical capabilities.
>
>I have looked at your helpful pages and I would like to visit them
>regularly in the future.
>
>Regards,
>Thomas
>>
>>>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
>>
>

--
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