help with query

Question

How to list the departments in Oracle's EMP table which have either two clerks or three managers?

Answer 1

There are a number of ways of getting this result. Here is one solution:

SQL> select deptno
  2         , mgr_count
  3         , clerk_count
  4  from
  5        ( select deptno
  6                 , sum ( case when job = 'MANAGER' then 1 else 0 end ) as mgr_count
  7                 , sum ( case when job = 'CLERK' then 1 else 0 end ) as clerk_count
  8          from emp
  9          group by deptno )
 10  where mgr_count > 3
 11  or clerk_count > 2
 12  /

    DEPTNO  MGR_COUNT CLERK_COUNT
---------- ---------- -----------
        30          3           5
        50          4           0

SQL>

Here is a slightly different approach:

SQL> select * from
  2      ( select deptno
  3         , job
  4         , count(*) as cnt
  5      from emp
  6      group by deptno, job
  7      )
  8  where ( job = 'MANAGER' and cnt >= 3 )
  9  or ( job = 'CLERK' and cnt >= 2 )
 10  /

    DEPTNO JOB              CNT
---------- --------- ----------
        20 CLERK              2
        30 CLERK              5
        30 MANAGER            3
        50 MANAGER            4

SQL>