Select a.taskid, a.hopid, a.avgBw, b.count0 / a.sumCount patio
from (select a.taskid, a.hopid, count(*) sumCount, avg(bw) avgBw
from tbl_task a
group by a.taskid, a.hopid) a,
(Select x.taskid, x.hopid, nvl(y.count0, 0) count0
from (select a.taskid, a.hopid
from tbl_task a
group by a.taskid, a.hopid) x,
(select a.taskid, a.hopid, count(*) count0
from tbl_task a
where a.neckconfindex = 0
group by a.taskid, a.hopid) y
where x.taskid = y.taskid(+)
and x.hopid = y.hopid(+)) b
where a.taskid = b.taskid
and a.hopid = b.hopid
|