1  with t as (select '2110:25:03' s from dual)
  2  select
  3   substr(s,1,instr(s,':')-1) h
  4  ,substr(s,instr(s,':')+1,instr(s,':',-1)-instr(s,':')-1) m
  5  ,substr(s,instr(s,':',-1)+1) s
  6* from t
SQL> /

H    M  S
---- -- --
2110 25 03

select 
to_number(substr(STATUSTRACKING, 0, (INSTR (STATUSTRACKING,':'))-1))*60*60 +
to_number(substr(STATUSTRACKING, -5, 2))*60 +
to_number(substr(STATUSTRACKING, -2, 2)) as ss
from TKSTATUS where STATUSTRACKING is not NULL

select round(avg(
to_number(substr(STATUSTRACKING, 0, (INSTR (STATUSTRACKING,':'))-1))*3600 + 
to_number(substr(STATUSTRACKING, -5, 2))*60 +
to_number(substr(STATUSTRACKING, -2, 2)))) as Srednee_znach
from TKSTATUS where STATUSTRACKING is not NULL 
    and status = 'ОТКРЫТ' 
    and CHANGEDATE between (sysdate -7) and (sysdate)

select round(avg(
to_number(substr(STATUSTRACKING, 0, (INSTR (STATUSTRACKING,':'))-1))*3600 + 
to_number(substr(STATUSTRACKING, -5, 2))*60 +
to_number(substr(STATUSTRACKING, -2, 2)))) as Srednee_znach
from TKSTATUS where STATUSTRACKING is not NULL 
    and status = 'ОТКРЫТ' 
    and CHANGEDATE between (sysdate -7) and (sysdate)

  1  select deptno,avg(sal_job) a from
  2   (select deptno,sum(sal) sal_job from emp group by deptno,job)
  3* group by deptno
SQL> /

    DEPTNO          A
---------- ----------
        30 3133,33333
        20       3625
        10 2916,66667

select 
round(avg(Vremya_v_statuse_v_sec)) as Srednee_znach_v_sec
from (
select t.ticketid, sum(
to_number(substr(tk.STATUSTRACKING, 0, (INSTR (tk.STATUSTRACKING,':'))-1))*3600 + 
to_number(substr(tk.STATUSTRACKING, -5, 2))*60 +
to_number(substr(tk.STATUSTRACKING, -2, 2))) as Vremya_v_statuse_v_sec
 from tkstatus tk
inner join TICKET t on t.TICKETID = tk.TICKETID
    where tk.STATUSTRACKING is not NULL 
    and tk.TICKETID = t.ticketid
    and tk.status = 'НАЗНАЧЕН' 
    and t.CLASS = 'ЗУ'
    and tk.CHANGEDATE between TO_TIMESTAMP('2017/05/29 00:00:00', 'yyyy/mm/dd hh24:mi:ss') 
        and TO_TIMESTAMP('2017/05/29 01:00:00', 'yyyy/mm/dd hh24:mi:ss')
group by t.TICKETID)

select avg(sum(sal)) av from emp
group by deptno
/
        AV
----------
      9675

select 
round(avg(sum(
to_number(substr(tk.STATUSTRACKING, 0, (INSTR (tk.STATUSTRACKING,':'))-1))*3600 + 
to_number(substr(tk.STATUSTRACKING, -5, 2))*60 +
to_number(substr(tk.STATUSTRACKING, -2, 2))))) as Srednee_vremya_v_sec
 from tkstatus tk
inner join TICKET t on t.TICKETID = tk.TICKETID
    where tk.STATUSTRACKING is not NULL 
    and tk.TICKETID = t.ticketid
    and tk.status = 'ОТКРЫТ' 
    and t.CLASS = 'ЗУ'
    and t.ACTUALFINISH between (trunc (TO_DATE(sysdate -1),'DDD')) and (trunc (TO_DATE(sysdate),'DDD'))
group by t.TICKETID

select 
(
(to_number(select count(t.ticketid) from ticket t where CLASS = 'ИНЦИДЕНТ' and  t.REPORTDATE between (trunc (TO_DATE(sysdate -1),'DDD')) and (trunc (TO_DATE(sysdate),'DDD'))))
/
(to_number(select count(t.ticketid) from ticket t where CLASS = 'ИНЦИДЕНТ' and  t.actualfinish between (trunc (TO_DATE(sysdate -1),'DDD')) and (trunc (TO_DATE(sysdate),'DDD'))))
)
as rez
 from TICKET

with ticket as(
select 'ИНЦИДЕНТ' CLASS, sysdate -2 REPORTDATE, sysdate    actualfinish from dual union all
select 'ИНЦИДЕНТ' CLASS, sysdate -1 REPORTDATE, sysdate -1 actualfinish from dual union all
select 'ИНЦИДЕНТ' CLASS, sysdate    REPORTDATE, sysdate -1 actualfinish from dual union all
select 'ИНЦИДЕНТ' CLASS, sysdate -2   REPORTDATE, sysdate -1    actualfinish from dual union all
select 'ИНЦИДЕНТ' CLASS, sysdate -3   REPORTDATE, sysdate    actualfinish from dual )

select count( case when t.REPORTDATE between trunc (sysdate -1,'DDD') and trunc (sysdate,'DDD') then 1 end)/count( case when t.actualfinish between trunc (sysdate -1,'DDD') and trunc (sysdate,'DDD') then 1 end) cnt

from ticket t 
where CLASS = 'ИНЦИДЕНТ' 
   and (t.REPORTDATE between trunc (sysdate -1,'DDD') and trunc (sysdate,'DDD')
      or t.actualfinish between trunc (sysdate -1,'DDD') and trunc (sysdate,'DDD')
         )