9 Replies Latest reply: Jul 26, 2013 9:12 AM by 954611 RSS

    Query with Groupby

    KODS

      Dear All,

       

      I am trying to write a proper quey to fetch data from database.

       

      Scenario:

      I need to retrive employees who are not working in multiple departments.

       

      scott@TESTCRM> select * from emp1;

       

           EMPNO     DEPTNO

      ---------- ----------

            7654         30

            7698         30

            7788         20

            7788         30

            7876         20

            7900         10

            7900         30

            7902         20

            7934         10

       

      scott@TESTCRM>

       

       

      Ouput Expected is

       

           EMPNO     DEPTNO

      ---------- ----------

            7654         30

            7698         30

            7876         20

            7902         20

            7934         10

       

      Thanks,

      Kods

        • 1. Re: Query with Groupby
          pollywog

          here is one way

          WITH t
               AS (SELECT 7654 empno, 30 deptno FROM DUAL
                   UNION ALL
                   SELECT 7698, 30 FROM DUAL
                   UNION ALL
                   SELECT 7788, 20 FROM DUAL
                   UNION ALL
                   SELECT 7788, 30 FROM DUAL
                   UNION ALL
                   SELECT 7876, 20 FROM DUAL
                   UNION ALL
                   SELECT 7900, 10 FROM DUAL
                   UNION ALL
                   SELECT 7900, 30 FROM DUAL
                   UNION ALL
                   SELECT 7902, 20 FROM DUAL
                   UNION ALL
                   SELECT 7934, 10 FROM DUAL),
               t1
               AS (  SELECT empno, COUNT (deptno)
                       FROM t
                   GROUP BY empno
                     HAVING COUNT (deptno) < 2)
          SELECT t.*
            FROM t, t1
           WHERE t.empno = t1.empno
          

          Here is another way

          WITH t
               AS (SELECT 7654 empno, 30 deptno FROM DUAL
                   UNION ALL
                   SELECT 7698, 30 FROM DUAL
                   UNION ALL
                   SELECT 7788, 20 FROM DUAL
                   UNION ALL
                   SELECT 7788, 30 FROM DUAL
                   UNION ALL
                   SELECT 7876, 20 FROM DUAL
                   UNION ALL
                   SELECT 7900, 10 FROM DUAL
                   UNION ALL
                   SELECT 7900, 30 FROM DUAL
                   UNION ALL
                   SELECT 7902, 20 FROM DUAL
                   UNION ALL
                   SELECT 7934, 10 FROM DUAL)
          SELECT empno, deptno
            FROM (SELECT empno, deptno, COUNT ('dracula') OVER (PARTITION BY empno) cnt
                    FROM t)
           WHERE cnt = 1
          

          I usually use count('dracula') over count('chocula') seems a little more efficient. I think count('of monte cristo') is available in 12c

          • 2. Re: Query with Groupby
            Pacmann

            Hi,

             

            select *

            from emp1 a

            where not exists (select null

                                from emp1 b

                                where a.empno = b.empno

                                   and a.depno <> b.depno)

            • 3. Re: Query with Groupby
              KODS

              Dear ,

               

              Thanks for quick reply. Please help me.

              Here is my scenario

               

              table : test

               

              Service        Status

              -----------    -------------

              111        Active

              111        Active

              111        In Active

              222        In Active

              222        In Active

              333        In Active

              333        Active

               

               

              Here I need to display only 222 record. Table contains 145698325 records.

              Need to display the record where the status is "In Active"

               

              I wrote a query but it is not giving the proper results. For reference I am published the query here.

               

              select service_id from asset

              group by service_id,status having count(service_id)=count(Status) and status='In Active';

               

              Thanks

              Kods

              • 4. Re: Query with Groupby
                Pacmann

                Like this ?

                 

                select service_id

                from asset

                group by service_id,status

                having count(*)>1 and sum(case when Status = 'In Active' then 1 else 0 end) = count(*)

                • 5. Re: Query with Groupby
                  KODS

                  Dear Pacmann,

                   

                  Query is close to my requirement. It is displaying the 111 and 333 records. But i need to display 222 only.

                   

                  Query should display only 222 because, even it is repeated twice its status is "In Active".

                  Query should not display records like 111 as it is associated with multiple status like "Active" and "In Active"

                   

                  Please help me.

                  • 6. Re: Query with Groupby
                    Etbin

                    Isn't this the same as https://forums.oracle.com/thread/2564036

                     

                    Regards

                     

                    Etbin

                    • 7. Re: Query with Groupby
                      Sven W.

                      How about this solution

                       

                      with testdata
                         as (select 111 as Service, 'Active' as Status from dual union all
                             select 111 as Service, 'Active' as Status from dual union all
                             select 111 as Service, 'Inactive' as Status from dual union all
                             select 222 as Service, 'Inactive' as Status from dual union all
                             select 222 as Service, 'Inactive' as Status from dual union all
                             select 333 as Service, 'Inactive' as Status from dual union all
                             select 333 as Service, 'Active' as Status from dual
                             )
                      select service
                      from testdata
                      group by service
                      having count(distinct status) = 1 and min(status) = 'Inactive';
                      
                      SERVICE
                      222
                      
                      • 8. Re: Query with Groupby
                        Pacmann

                        Oh sorry,

                        i didn't remove the status from group by... (even if i don't understand how they could be in your output considering you input)

                         

                        select service_id

                        from asset

                        group by service_id

                        having count(*)>1 and sum(case when Status = 'In Active' then 1 else 0 end) = count(*)

                        • 9. Re: Query with Groupby
                          954611

                          select empno, count(deptno) as countx  from emp1 group by empno having co

                          untx=1;

                          +-------+--------+

                          | empno | countx |

                          +-------+--------+

                          |  7654 |      1 |

                          |  7698 |      1 |

                          |  7876 |      1 |

                          |  7902 |      1 |

                          |  7934 |      1 |

                          +-------+--------+