-3

I have this table:

Name                       Null?    Type
-------------------------- -------- ------------
EMPLOYEENO                 NOT NULL NUMBER(4)
ENAME                               VARCHAR2(15)
JOB                                 VARCHAR2(15)
MGR                                 NUMBER(4)
HIREDATE                            DATE
SAL                                 NUMBER
COMM                                NUMBER
DEPTNO                              NUMBER(2).

I want to get the department with minimum employees who have a given job (for example all the employees with 'Analyst' job).

Can you please help me with the query?

APC
  • 144,005
  • 19
  • 170
  • 281
Chirila Vasile
  • 359
  • 1
  • 2
  • 11

2 Answers2

1

Here the key is to get the count of Employee doing particular job in each department. In below query, this is achieved by subquery. Then, we want to get the Department with minimum no. of employee doing that job so we ordered the records returned by subquery in ascending and then select the first result by using rownum = 1

SELECT DEPTNO from ( SELECT COUNT(*) AS NO_OF_EMP , DEPTNO FROM EMPLOYEE EMP WHERE EMP.JOBNAME = 'Analyst' GROUP BY DEPTNO ORDER BY NO_OF_EMP ASC ) where ROWNUM = 1;

codiacTushki
  • 750
  • 1
  • 9
  • 22
  • @TusharShimpi . . . This will not return a department that has zero analysts, which would be the minimum as I understand the question. – Gordon Linoff Mar 28 '15 at 14:18
0

Given that the minimum number of such employees could be 0, you need to be a bit clever about how you do this:

select d.*
from (select deptno,
             sum(case when jobname = 'Analyst' then 1 else 0 end) as numAnalysts
      from employees
      group by deptno
      order by numAnalysts asc
     ) d
where rownum = 1;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786