Oracle SQL query for distribution

Question

I have a table PROCESSES with columns PR_ID, PR_FROM, PR_TO. PR_ID is process identifier and PR_FROM and PR_TO timestamps defining when process started and when it finished.

Now I'd like to create an Oracle SQL query which will represent how many processes lasted 0-1 hour, how many lasted 1-2 hours and so on. How to create such a query?

Answer 1

I found the answer myself (distribution within 24 hours):

SELECT h,COUNT(pr_id) n
FROM (SELECT LEVEL AS h FROM DUAL CONNECT BY LEVEL <= 24)
LEFT JOIN processes ON h=(EXTRACT(HOUR FROM (PR_TO-PR_FROM))+1)
GROUP BY h
ORDER BY h

distribution from 0 to max value:

SELECT h,COUNT(pr_id) n
FROM (SELECT LEVEL AS h FROM DUAL CONNECT BY LEVEL <= ((SELECT MAX(EXTRACT(HOUR FROM (PR_TO-PR_FROM))+1) FROM processes)))
LEFT JOIN processes ON h=(EXTRACT(HOUR FROM (PR_TO-PR_FROM))+1)
GROUP BY h
ORDER BY h

Robert helped a lot.

Answer 2

SELECT TRUNC((PR_FROM - PR_TO)*24),count(*)
FROM PROCESSES 
GROUP BY TRUNC((PR_FROM - PR_TO)*24)

EDIT Show 0 occurance records

with hours as (select level-1 as lev
               from dual
               connect by level <= (select max(trunc((pr_from - pr_to)*24))
                                    from processes
                                    ) + 1
              )
,    proctime as (select trunc((pr_from - pr_to)*24) as hours 
                  ,      count(*) as num
                  from processes
                  group by trunc((pr_from - pr_to)*24))   
select lev
,      nvl(num,0) from hours
left outer join proctime on (lev = hours)
order by 1;