i've one table during my database say mytable, which contents request originating from other source. There's one column within this table as Time, which stores time and date(e.g. 2010/07/10 01:21:43) when request was received. Now i wish to fetch the information out of this table on hourly grounds for every day. Means i would like count of demands database receive in every hrs of the day. e.g.for 1 o'clock to two o'clock say count is 50 ..such as this.. I'll run this question in the finish of day. And so i can get demands received per day group by hourly.
Can anybody assist me to within this.
I would like query that will take a shorter period to fetch the information as my database dimensions are huge.
Any othre way than OMG Ponies answer.
Make use of the TO_CHAR function to format the DATETIME column, so that you can GROUP Because of it for aggregate functions:
SELECT TO_CHAR(t.time, 'YYYY-MM-DD HH24') AS hourly, COUNT(*) AS numPerHour FROM YOUR_TABLE t GROUP BY TO_CHAR(t.time, 'YYYY-MM-DD HH24')
Why not create another table that stores the count and also the date. Produce a database job which will run hourly and set the count and sysdate within the new table. Your code is going to be just querying the brand new table.
create table ohtertable_count (no_of_rows number, time_of_count date);
Your database job, which will run hourly is going to be something similar to
And you'll query the table othertable_count rather than querying your original table.
insert into othertable_count select count(1), sysdate from othertable;