5

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.

Mehdi
  • 4,396
  • 4
  • 29
  • 30

1 Answers1

8

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');
A.B.Cade
  • 16,735
  • 1
  • 37
  • 53
  • Thanks A.B.Cade it works but I don't see hour in first column. i just see day-month-year ! I think it's because of trunc function. how can I use it to show hour too? may be it's because of sql developer that does not show hour? By the way how about the second and third query? – Mehdi May 29 '12 at 12:58
  • Try to replace the `trunc(d1, 'hh')` with `to_char(trunc(d1, 'hh'), 'yyyy-mm-dd hh24')`. As for the other queries, they should be the same just trunc them according to "day" with `dd` or "month" with `mm` (just replace the `hh`) – A.B.Cade May 29 '12 at 13:12
  • @A.B.Cade I think this query is returning wrong results for dd and mm. I'm not familiar with connect by command but I think we should change the number 24 when using dd or mm. It works perfect for hh. even in your fiddle the result for mm and dd is wrong. execute your connect by part with dd and mm and you will see that there are more that one record for each day or month! – Mehdi May 30 '12 at 04:49
  • this is right : `select to_char(trunc(mnd + (level/24),'hh'),'yyyy-mm-dd hh24') ddd from (select min(copied_timestamp) mnd, max(copied_timestamp) mxd from req) v connect by mnd + (level/24) <= mxd;` but this is wrong `select to_char(trunc(mnd + (level/24),'dd'),'yyyy-mm-dd') ddd from (select min(copied_timestamp) mnd, max(copied_timestamp) mxd from req) v connect by mnd + (level/24) <= mxd;` – Mehdi May 30 '12 at 04:52
  • 1
    You are righ, of course, level is an integer representing the itteration, if you add it to a `date` type it adds days, so only for hours it makes sense to devide by 24. Fix is simple: for days use `mnd+level` and for months use `add_months(mnd, level)`. I thought of another thing- level starts from 1 so basically you need to use `level-1`. I also thought that it should be better to truncate the min/max dates in the inner query. So, here is the new fiddle: http://sqlfiddle.com/#!4/6b5f5/21 .If it's working right then I'll update my answer (the fiddle won't stay forever...) – A.B.Cade May 30 '12 at 06:04
  • @A.B.Cade excellent. They work very well. Before you reply I fixed the query and when I wanted to reply, I lost the POWER and after turning on, lost all fixed code in sqlDeveloper ;) – Mehdi May 30 '12 at 08:18
  • By the way this query is a little slow when choosing group by hour because connect by generated lots of records for long ranges. But no problem if this is an efficient way for this kind of query. I will mark this answer accepted ;) and please edit the answer. Thanks a lot A.B.Cade – Mehdi May 30 '12 at 08:22
  • WOW, we were writing comments at the same time! – Mehdi May 30 '12 at 08:23