Skip to Main Content
  • Questions

  • Count # of records by grouping by number of days

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Carol.

Asked: January 16, 2006 - 6:34 pm UTC

Last updated: January 19, 2006 - 8:12 am UTC

Version: 9

Viewed 1000+ times

You Asked

Hi Tom,
I want to subtract 2 date columns to get the number of days between the two dates and then count the number of records in each of these groups. Could you please tell me the best way to do this? Thanks very much.

0-15 days: 300
16-30 days: 10
31-60 days: 5
61-90 days: 15
Over 90 days: 100



and Tom said...

select what, count(*)
from (
select case
when dys < 16 then 'a 0-15'
when dys < 31 then 'b 16-30'
when dys < 61 then 'c 31-60'
when dys < 91 then 'd 61-90'
when dys >= 91 then 'e over 90'
end what
from (
select dt1-dt2 dys
from t
)
)
group by what
order by what
/


can do that....

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, January 16, 2006 - 11:05 pm UTC


Can 0 values be displayed?

A reader, January 18, 2006 - 1:24 pm UTC

If one or more of the ranges has a total of 0 records, is there any way that can be displayed?
For example:
0-15 days: 300
16-30 days: 0
31-60 days: 0
61-90 days: 15
Over 90 days: 0


Tom Kyte
January 19, 2006 - 8:12 am UTC 

you would need to have a small set somewhere to outer join to.


ops$tkyte@ORA9IR2> with data
  2  as
  3  (select level l,
  4         case
  5         when level = 1 then '0-15'
  6         when level = 2 then '16-30'
  7         when level = 3 then '31-60'
  8         when level = 4 then '61-90'
  9         when level = 5 then 'over 90'
 10     end what
 11    from dual
 12  connect by level <= 5)
 13  select data.what, count(t.what)
 14    from ( select case
 15                  when dt1-dt2 < 16 then '0-15'
 16                  when dt1-dt2 < 31 then '16-30'
 17                  when dt1-dt2 < 61 then '31-60'
 18                  when dt1-dt2 < 91 then '61-90'
 19                  when dt1-dt2 >= 91 then 'over 90'
 20                  end what
 21             from (select last_ddl_time dt1, created dt2 from all_objects)
 22         ) t right outer join data on (data.what = t.what)
 23    group by data.what, data.l
 24    order by data.l
 25  /
 
WHAT                           COUNT(T.WHAT)
------------------------------ -------------
0-15                                   27052
16-30                                      1
31-60                                      0
61-90                                      2
over 90                                 1366

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.