This discussion is archived
11 Replies Latest reply: Oct 4, 2012 12:28 AM by jeneesh RSS

Analytical query help

prakash Pro
Currently Being Moderated
Hi all,

I want to display count as 0 if there is one clerk and one Analyst working in dept no 10 and if there is atleast one Clerk in other departments . If this condition fails then i have to display 1 as count .


Oracle Version 

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
PL/SQL Release 11.1.0.7.0 - Production
CORE    11.1.0.7.0      Production
TNS for 32-bit Windows: Version 11.1.0.7.0 - Production
NLSRTL Version 11.1.0.7.0 - Production
DROP TABLE DEPT;
CREATE TABLE DEPT
       (DEPTNO NUMBER(2) CONSTRAINT PK_DEPT PRIMARY KEY,
     DNAME VARCHAR2(14) ,
     LOC VARCHAR2(13) ) ;
DROP TABLE EMP;
CREATE TABLE EMP
       (EMPNO NUMBER(4) CONSTRAINT PK_EMP PRIMARY KEY,
     ENAME VARCHAR2(10),
     JOB VARCHAR2(9),
     MGR NUMBER(4),
     HIREDATE DATE,
     SAL NUMBER(7,2),
     COMM NUMBER(7,2),
     DEPTNO NUMBER(2) CONSTRAINT FK_DEPTNO REFERENCES DEPT);
INSERT INTO DEPT VALUES
     (10,'ACCOUNTING','NEW YORK');
INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS');
INSERT INTO DEPT VALUES
     (30,'SALES','CHICAGO');
INSERT INTO DEPT VALUES
     (40,'OPERATIONS','BOSTON');
INSERT INTO EMP VALUES
(7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20);
INSERT INTO EMP VALUES
(7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30);
INSERT INTO EMP VALUES
(7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30);
INSERT INTO EMP VALUES
(7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20);
INSERT INTO EMP VALUES
(7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30);
INSERT INTO EMP VALUES
(7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30);
INSERT INTO EMP VALUES
(7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10);
INSERT INTO EMP VALUES
(7788,'SCOTT','ANALYST',7566,to_date('13-JUL-87')-85,3000,NULL,20);
INSERT INTO EMP VALUES
(7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10);
INSERT INTO EMP VALUES
(7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30);
INSERT INTO EMP VALUES
(7876,'ADAMS','CLERK',7788,to_date('13-JUL-87')-51,1100,NULL,20);
INSERT INTO EMP VALUES
(7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30);
INSERT INTO EMP VALUES
(7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20);
INSERT INTO EMP VALUES
(7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10);
Thanks,
P Prakash
  • 1. Re: Analytical query help
    jeneesh Guru
    Currently Being Moderated
    prakash wrote:
    Hi all,

    I want to display count as 0 if there is one clerk and one Analyst working in dept no 10 and if there is atleast one Clerk in other departments . If this condition fails then i have to display 1 as count .
    Hope this is a home work?

    Hows much you tried so far?
  • 2. Re: Analytical query help
    prakash Pro
    Currently Being Moderated
    hi,

    This is not my home work, my requirement is some what similar to this that is why i have posted here.
    let me post what my sample data and what i have tried so that it will be clear.

    Thanks for quick response.



    Thanks,
    P Prakash
  • 3. Re: Analytical query help
    jeneesh Guru
    Currently Being Moderated
    prakash wrote:
    Hi all,
    I want to display count as 0 if there is one clerk and one Analyst working in dept no 10 and if there is atleast one Clerk in other departments . If this condition fails then i have to display 1 as count .
    SQL> select deptno,job from emp order by 1;
    
        DEPTNO JOB
    ---------- ---------
            10 MANAGER
            10 PRESIDENT
            10 CLERK
            20 MANAGER
            20 ANALYST
            20 CLERK
            20 CLERK
            20 ANALYST
            30 SALESMAN
            30 SALESMAN
            30 SALESMAN
            30 CLERK
            30 MANAGER
            30 SALESMAN
    
    14 rows selected.
    
    SQL> select least(
      2               max(decode(deptno,10,decode(job,'CLERK',1,0),0)),
      3               max(decode(deptno,10,decode(job,'ANALYST',1,0),0)),
    
      4               max(decode(deptno,10,0,decode(job,'CLERK',1,0)))
      5        ) cnt
      6  from emp;
    
           CNT
    ----------
             0
    
    SQL> select least(
      2               max(decode(deptno,20,decode(job,'CLERK',1,0),0)),
      3               max(decode(deptno,20,decode(job,'ANALYST',1,0),0)),
    
      4               max(decode(deptno,20,0,decode(job,'CLERK',1,0)))
      5        ) cnt
      6  from emp;
    
           CNT
    ----------
             1
    Edited by: jeneesh on Oct 4, 2012 10:30 AM

    Edited by: jeneesh on Oct 4, 2012 10:42 AM
    Two LEAST not required..
  • 4. Re: Analytical query help
    jeneesh Guru
    Currently Being Moderated
    And, if you want to show for all departments atonce
    SQL> select deptno,
      2         least(
      3             max(decode(job,'CLERK',1,0)),
      4             max(decode(job,'ANALYST',1,0)),
      5            (select count(*)
      6             from emp e2
      7             where e2.deptno != e1.deptno
      8        and job = 'CLERK'
      9             and rownum = 1)
     10            ) cnt
     11  from emp e1
     12  group by deptno
     13  order by 1;
    
        DEPTNO        CNT
    ---------- ----------
            10          0
            20          1
            30          0
    Edited by: jeneesh on Oct 4, 2012 10:41 AM
  • 5. Re: Analytical query help
    prakash Pro
    Currently Being Moderated
    looking for some more answers
  • 6. Re: Analytical query help
    Rahul_India Journeyer
    Currently Being Moderated
    do you have any alternate solutions jeenesh
    select job,deptno,case
    when (UPPER(JOB)='ANALYST' and deptno=10 ) OR upper(job)='CLERK' 
    THEN
    0
    ELSE 
    1
    END AS count
    
    from emp 
    
    ANSWER
    JOB         DEPT NO    COUNT
    ------         --------------  -------------
    CLERK     20     0
    SALESMAN     30     1
    SALESMAN     30     1
    MANAGER     20     1
    SALESMAN     30     1
    MANAGER     30     1
    MANAGER     10     1
    ANALYST     20     1
    PRESIDENT     10     1
    SALESMAN     30     1
    CLERK     20     0
    CLERK     30     0
    ANALYST     20     1
    CLERK     10     0
    Edited by: Rahul_India on Oct 3, 2012 11:25 PM
  • 7. Re: Analytical query help
    jeneesh Guru
    Currently Being Moderated
    Rahul_India wrote:
    do you have any alternate solutions jeenesh
    If you want to display all the details:
    SQL> select deptno,ename,job,
      2             least(
      3                 max(decode(job,'CLERK',1,0)) over(partition by deptno),
      4                 max(decode(job,'ANALYST',1,0)) over(partition by deptno),
      5                (select count(*)
      6                 from emp e2
      7                 where e2.deptno != e1.deptno
      8                 and job = 'CLERK'
      9                 and rownum = 1)
     10               ) cnt
     11     from emp e1
     12     --group by deptno
     13     order by 1,2;
    
        DEPTNO ENAME      JOB              CNT
    ---------- ---------- --------- ----------
            10 CLARK      MANAGER            0
            10 KING       PRESIDENT          0
            10 MILLER     CLERK              0
            20 ADAMS      CLERK              1
            20 FORD       ANALYST            1
            20 JONES      MANAGER            1
            20 SCOTT      ANALYST            1
            20 SMITH      CLERK              1
            30 ALLEN      SALESMAN           0
            30 BLAKE      MANAGER            0
            30 JAMES      CLERK              0
            30 MARTIN     SALESMAN           0
            30 TURNER     SALESMAN           0
            30 WARD       SALESMAN           0
    
    14 rows selected.
  • 8. Re: Analytical query help
    Rahul_India Journeyer
    Currently Being Moderated
    jeenesh is by solution correct?
    and how cani modify my solution so as to display only deptno and count
  • 9. Re: Analytical query help
    jeneesh Guru
    Currently Being Moderated
    Rahul_India wrote:
    jeenesh is by solution correct?
    and how cani modify my solution so as to display only deptno and count
    It is not correct..

    You are checking single row at a time..

    But the requirement is to check across the table..
  • 10. Re: Analytical query help
    Rahul_India Journeyer
    Currently Being Moderated
    ok when we need to check across the table like in this case what method or approach we should follow
  • 11. Re: Analytical query help
    jeneesh Guru
    Currently Being Moderated
    Rahul_India wrote:
    ok when we need to check across the table like in this case what method or approach we should follow
    Analytic Functions

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points