How to display Categories with no records in a query

 In the below example we have dept numbers 10,20,30 only in emp table.
when we try to count number of records in each dept we get only 10,20 and 30.
But the client says that he needs all the dept counts and if there are no records with in the dept 40 and 50 he should get 0 records.

In this situation we can use below query to achieve it. you can also achieve it by creating one dummy table with dummy column which includes all the dept numbers and use outer join with emp table to get this result.

select a.deptnum,nvl(b.cnt,0) from (select '10' deptnum from dual
            union select '20' deptnum from dual
            union select '30' deptnum from dual
            union select '40' deptnum from dual
            union select '50' deptnum from dual)a,(select case when deptno=10 then '10'
            when deptno=20 then '20'
            when deptno=30 then '30'
            when deptno=40 then '40'
            when deptno=50 then '50'
            else 'Others' end deptnum, count(*) cnt from
            emp
            group by
            case when deptno=10 then '10'
            when deptno=20 then '20'
            when deptno=30 then '30'
            when deptno=40 then '40'
            when deptno=50 then '50'
            else 'Others' end) b
            where a.deptnum=b.deptnum(+)