11 Replies Latest reply: Oct 4, 2012 2:28 AM by jeneesh RSS

    Analytical query help

    prakash
      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
          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
            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
              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
                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
                  looking for some more answers
                  • 6. Re: Analytical query help
                    Rahul_India
                    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
                      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
                        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
                          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
                            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
                              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