I have written a query that counts records hour by hour:
select TO_CHAR(copied_timestamp, 'YYYY-MM-DD HH24'),count(*) from req group by
TO_CHAR(copied_timestamp, 'YYYY-MM-DD HH24');
the result is:
2012-02-22 13 2280
2012-02-22 15 1250
2012-02-22 16 1245
2012-02-22 19 1258
But I need a result like this:
2012-02-22 13 2280
2012-02-22 14 0
2012-02-22 15 1250
2012-02-22 16 1245
2012-02-22 17 0
2012-02-22 18 0
2012-02-22 19 1258
Also I have these queries that group by day and month too!
select TO_CHAR(copied_timestamp, 'YYYY-MM-DD'),count(*) from req
group by TO_CHAR(copied_timestamp, 'YYYY-MM-DD');
select TO_CHAR(copied_timestamp, 'YYYY-MM'),count(*) from req
group by TO_CHAR(copied_timestamp, 'YYYY-MM');
I need their gaps to be filled with zero or null too.
Any help is really appreciated.
try:
first query (by hour):
with t as (
select mnd + ((level-1)/24) ddd
from
(select trunc(min(copied_timestamp),'hh') mnd, trunc(max(copied_timestamp),'hh') mxd from req) v
connect by mnd + ((level-1)/24) <= mxd
)
select to_char(trunc(d1, 'hh'), 'yyyy-mm-dd hh24'), count(d2) from
(select nvl(copied_timestamp, ddd) d1, copied_timestamp d2 from req right outer join (
select ddd from t) ad on ddd = trunc(copied_timestamp, 'hh'))
group by trunc(d1, 'hh');
second query (by day):
with t as (
select mnd + level-1 ddd
from
(select trunc(min(copied_timestamp),'dd') mnd, trunc(max(copied_timestamp),'dd') mxd from req) v
connect by mnd + level-1 <= mxd
)
select to_char(trunc(d1, 'dd'), 'yyyy-mm-dd'), count(d2) from
(select nvl(copied_timestamp, ddd) d1, copied_timestamp d2 from req right outer join (
select ddd from t) ad on ddd = trunc(copied_timestamp, 'dd'))
group by trunc(d1, 'dd');
third query (by month):
with t as (
select add_months(mnd, level-1) ddd
from
(select trunc(min(copied_timestamp),'mm') mnd, trunc(max(copied_timestamp),'mm') mxd from req) v
connect by add_months(mnd, level-1) <= mxd
)
select to_char(trunc(d1, 'mm'), 'yyyy-mm'), count(d2) from
(select nvl(copied_timestamp, ddd) d1, copied_timestamp d2 from req right outer join (
select ddd from t) ad on ddd = trunc(copied_timestamp, 'mm'))
group by trunc(d1, 'mm');