select a.deptno ,
sum(case when sal<3000 then 1 else 0 end) ,
sum(case when sal<=6000 and sal>=3000 then 1 else 0 end) ,
sum(case when sal>6000 then 1 else 0 end) ,
nvl(sum(sal),0)
from dept a, emp b
where a.deptno = b.deptno(+)
group by a.deptno
order by a.deptno;
