select a.aa ,nvl(b.bb ,0) bb from
(select id,sum(CNum) aa from TT group by ID) a,
(select id,sum(cnum) bb from tt where L >=5 group by id) b
where a.id =b.id(+)
(select id,sum(CNum) aa from TT group by ID) a,
(select id,sum(cnum) bb from tt where L >=5 group by id) b
where a.id =b.id(+)
where L>=5 group by ID