topic Aggregation Question in Operating System - HP-UX

Aggregation Question

Edgar_10 — Wed, 23 Jul 2003 08:46:44 GMT

Hi,

Anyone know the syntax to generate a list of a count for records over a 24 day period.What I want to know is query the database and get records stored in table date field per hour.

Thanks in advance!

Re: Aggregation Question

Yogeeraj_1 — Wed, 23 Jul 2003 09:54:27 GMT

hi,

can you try something like this one:

select mnumrows, to_char(mdate, 'dd/mm/yyyy hh24:mi')
from (
select count(*) mnumrows, trunc(trndat,'HH') mdate
from mytable
where trndat > sysdate-25
group by trunc(trndat,'HH')
)
/

hope this helps!
regards
Yogeeraj

Re: Aggregation Question

Yogeeraj_1 — Wed, 23 Jul 2003 10:01:40 GMT

hi again,

This is what i get on my test database (output has been limited to first 10 records)

yd@MYDB.MU> r
1 select mnumrows, to_char(mdate, 'dd/mm/yyyy hh24:mi')
2 from (
3 select count(*) mnumrows, trunc(trndat,'HH') mdate
4 from mytable
5 where trndat > sysdate-25
6 group by trunc(trndat,'HH'))
7* where rownum < 11

MNUMROWS TO_CHAR(MDATE,'D
__________ ________________
9 28/06/2003 14:00
46 28/06/2003 15:00
396 30/06/2003 08:00
403 30/06/2003 09:00
579 30/06/2003 10:00
414 30/06/2003 11:00
219 30/06/2003 12:00
604 30/06/2003 13:00
229 30/06/2003 14:00
230 30/06/2003 15:00

10 rows selected.

Elapsed: 00:00:00.16
yd@MYDB.MU>

Am sure other solutions do exits if you use Analytic Functions.

best regards
Yogeeraj

Re: Aggregation Question

Edgar_10 — Wed, 23 Jul 2003 10:21:15 GMT

Hi Yogee!

Thanks man, You pointed me in the right directiorn!

Most appraciated!