sql - Show job, total number of employees under each job from emp table -

i want output like:

----------   job          count          ename ---------- salesman      4            name1                            name2                            name3                            name4  clerk         4            name1                            name2                            name3                            name4 manager       3            name1                            name2                            name3 analyst       2            name1                            name2 president     1            name 

.......and on.

it shoud not repeat job title , count each name in job. have got answer repetition.

sql fiddle

oracle 11g r2 schema setup:

create table employee     ("job" varchar2(9), "ename" varchar2(5)) ;  insert      employee ("job", "ename")          values ('salesman', 'name1')     employee ("job", "ename")          values ('salesman', 'name4')     employee ("job", "ename")          values ('clerk', 'name1')     employee ("job", "ename")          values ('clerk', 'name2')     employee ("job", "ename")          values ('manager', 'name1')     employee ("job", "ename")          values ('manager', 'name2')     employee ("job", "ename")          values ('manager', 'name3')     employee ("job", "ename")          values ('salesman', 'name2')     employee ("job", "ename")          values ('salesman', 'name3')     employee ("job", "ename")          values ('clerk', 'name3')     employee ("job", "ename")          values ('president', 'name')     employee ("job", "ename")          values ('clerk', 'name4')     employee ("job", "ename")          values ('analyst', 'name1')     employee ("job", "ename")          values ('analyst', 'name2') select * dual ; 

query 1:

select case when j."ename" = firstname j."job" else ' ' end job,         case when j."ename" = firstname cast(c.cnt varchar(5)) else ' ' end "count",         j."ename" employee j inner join (select "job",  min("ename") firstname, count(*) cnt employee group "job") c on c."job" = j."job" order cnt desc, j."job", j."ename" 

results:

|       job | count | ename | |-----------|-------|-------| |     clerk |     4 | name1 | |           |       | name2 | |           |       | name3 | |           |       | name4 | |  salesman |     4 | name1 | |           |       | name2 | |           |       | name3 | |           |       | name4 | |   manager |     3 | name1 | |           |       | name2 | |           |       | name3 | |   analyst |     2 | name1 | |           |       | name2 | | president |     1 |  name |