1 2 Previous Next 20 Replies Latest reply: Jan 9, 2013 5:54 AM by user8680179 RSS

    SQL Query for IN,GroupBy and count(*)

    user8680179
      DDL STMT.
      ---------
      CREATE TABLE EMP (
           STAFFNO NUMBER,
           PRESENT_DATE DATE,
           STATUS VARCHAR2(25),
           DURATIONINHOUR NUMBER
      );

      DML STMT.
      --------
      INSERT INTO EMP(STAFFNO, PRESENT_DATE, STATUS, DURATIONINHOUR) VALUES (1, to_date('12/05/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'PRESENT', 8);
      INSERT INTO EMP(STAFFNO, PRESENT_DATE, STATUS, DURATIONINHOUR) VALUES (1, to_date('12/06/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'PRESENT', 9);
      INSERT INTO EMP(STAFFNO, PRESENT_DATE, STATUS, DURATIONINHOUR) VALUES (1, to_date('12/08/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'NOT PRESENT', 9);
      INSERT INTO EMP(STAFFNO, PRESENT_DATE, STATUS, DURATIONINHOUR) VALUES (1, to_date('12/13/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'PRESENT', 10);
      INSERT INTO EMP(STAFFNO, PRESENT_DATE, STATUS, DURATIONINHOUR) VALUES (2, to_date('12/05/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'PRESENT', 10);
      INSERT INTO EMP(STAFFNO, PRESENT_DATE, STATUS, DURATIONINHOUR) VALUES (2, to_date('12/06/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'PRESENT', 10);
      INSERT INTO EMP(STAFFNO, PRESENT_DATE, STATUS, DURATIONINHOUR) VALUES (3, to_date('12/05/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'PRESENT', 8);
      INSERT INTO EMP(STAFFNO, PRESENT_DATE, STATUS, DURATIONINHOUR) VALUES (3, to_date('12/06/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'PRESENT', 9);


      There are three inputs for my sql query
      1. Date Interval 1 : to_date('12/04/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS') and to_date('12/07/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS')
      2. Date Interval 1 : to_date('12/12/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS') and to_date('12/14/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS')
      3.also minimum occurrence of staff no. should be grater than 3(*including both date interval*).

      I need all those 'STAFFNO' whose status is 'PRESENT' in DateInterval1 as well as in DateInterval2 and also i need sum of 'DURATIONINHOUR' column for this condition.

      As in the above example for 'STAFFNO' 1 there are 2 rows present for present_date Dec,05 and Dec,06 {In date interval Dec,04 and Dec,07}
      and 1 row is present for present_date Dec,13{In date interval Dec,12 and Dec,14}
      so STAFFNO' 1 shall be displayed in output with SUM(DURATIONINHOUR)=*27*(8+9+10).

      OUTPUT Shall be
      STAFFNO,SUM(DURATIONINHOUR)=(1,27)




      I tried to write sql query but not able to sum 'DURATIONINHOUR' column and group by count(*) query

      select staffno from
      (select staffno
                          from
                               emp
                          WHERE present_date between to_date('12/04/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS') AND to_date('12/07/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS') AND
                               status='PRESENT'
      )
      where staffno IN
      (
      select staffno
                          from
                               emp
                          WHERE present_date between to_date('12/12/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS') AND to_date('12/14/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS') AND
                               status='PRESENT'
      )
        • 1. Re: SQL Query for IN,GroupBy and count(*)
          Bawer
          select staffno, sum (DURATIONINHOUR)
          from
          emp
          WHERE 
          (
          (present_date between to_date('12/04/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS') AND to_date('12/07/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS') )
          or
          (present_date between to_date('12/12/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS') AND to_date('12/14/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS') )
          or
          DURATIONINHOUR > 3
          )
          AND status='PRESENT'
          group by staffno
          • 2. Re: SQL Query for IN,GroupBy and count(*)
            chris227
            As the status column already indicates the data being in the recommended date intervals the follwoing seems to be sufficend
            select
             staffno
            ,SUM(
              case status when 'PRESENT' then DURATIONINHOUR end
             )
            sum
            from 
            emp
            group by
            staffno
            having (
            count(*)>3
            )
            with date intervals
            with
            EMP(STAFFNO, PRESENT_DATE, STATUS, DURATIONINHOUR) as (
            Select 1, to_date('12/05/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'PRESENT', 8 from dual union all
            Select 1, to_date('12/06/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'PRESENT', 9 from dual union all
            Select 1, to_date('12/08/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'NOT PRESENT', 9 from dual union all
            Select 1, to_date('12/13/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'PRESENT', 10 from dual union all
            Select 2, to_date('12/05/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'PRESENT', 10 from dual union all
            Select 2, to_date('12/06/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'PRESENT', 10 from dual union all
            Select 3, to_date('12/05/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'PRESENT', 8 from dual union all
            Select 3, to_date('12/06/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'PRESENT', 9 from dual
            )
            
            select
             staffno
            ,SUM(
              case status when 'PRESENT' then DURATIONINHOUR end
             )
            sum
            from 
            emp e
            group by
            staffno
            ,
            case
            when present_date
            between to_date('12/04/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS')
                AND to_date('12/07/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS')
            then 1
            when present_date
            between to_date('12/12/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS')
                AND to_date('12/14/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS')
            then 1
            else 0
            end
            having (
            case
            when present_date
            between to_date('12/04/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS')
                AND to_date('12/07/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS')
            then 1
            when present_date
            between to_date('12/12/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS')
                AND to_date('12/14/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS')
            then 1
            else 0
            end > 0
            )
            and
            (select count(*) from emp where staffno = e.staffno) > 3
            Edited by: chris227 on 09.01.2013 01:02
            corrected

            Edited by: chris227 on 09.01.2013 01:11
            query with date intervals added

            Edited by: chris227 on 09.01.2013 01:12

            Edited by: chris227 on 09.01.2013 01:15
            • 3. Re: SQL Query for IN,GroupBy and count(*)
              Purvesh K
              Below is one way to achieve it:
              select staffno,
                     sum(sm) sum_durationhour
                from (
                      select staffno, 
                             case
                                  when present_date between to_date('12/04/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS') and to_date('12/07/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS')
                                    then 'GRP1'
                                  when present_date between to_date('12/12/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS') and to_date('12/14/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS')
                                    then 'GRP2'
                                end grp,
                             count(*) cnt,
                             sum(durationinhour) sm
                        from emp
                       where (present_date between to_date('12/04/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS') and to_date('12/07/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS')
                          or present_date between to_date('12/12/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS') and to_date('12/14/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS')
                             )
                         and status = 'PRESENT'
                       group by staffno, case
                                  when present_date between to_date('12/04/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS') and to_date('12/07/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS')
                                    then 'GRP1'
                                  when present_date between to_date('12/12/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS') and to_date('12/14/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS')
                                    then 'GRP2'
                                end
                     )
               group by staffno
               having sum(cnt) >= 3;
              
              STAFFNO                SUM_DURATIONHOUR       
              ---------------------- ---------------------- 
              1                      27
              Edited by: Purvesh K on Jan 9, 2013 2:25 PM
              Added Status condition. Was missed earlier
              • 4. Re: SQL Query for IN,GroupBy and count(*)
                Purvesh K
                chris227 wrote:
                present_date between to_date('12/12/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS')
                AND to_date('12/14/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS')
                AND
                present_date between to_date('12/04/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS')
                AND to_date('12/07/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS')
                13/12/2012 date is between 12/12/2012 and 14/12/2012 but not between 04/12/2012 and 07/12/2012. So the condition would not fetch any records, would it?
                • 5. Re: SQL Query for IN,GroupBy and count(*)
                  chris227
                  yep, still sleeping i guess ...
                  Perhaps the date predicates are not needed at all, see my correction above done meanwhile.
                  • 6. Re: SQL Query for IN,GroupBy and count(*)
                    Purvesh K
                    chris227 wrote:
                    yep, still sleeping i guess ...
                    Perhaps the date predicates are not needed at all, see my correction above done meanwhile.
                    In the present set of data provided by OP, perhaps not required to use date predicates. But with actual data, I think it might be required.

                    Deciding on it would, IMO, discretion of OP. I liked your nice, short solution as opposed to mine, which is bulky.
                    • 7. Re: SQL Query for IN,GroupBy and count(*)
                      chris227
                      Purvesh K wrote:
                      Deciding on it would, IMO, discretion of OP. I liked your nice, short solution as opposed to mine, which is bulky.
                      Mine too now.

                      One thing on count(*)>=3.
                      OP wrote that
                      "also minimum occurrence of staff no. should be grater than 3(*including both date interval*)."

                      So if you include the PRESENT-predicate in the where clause of the group by having sum(cnt) >= 3 would not be the proper translation of this probably.
                      • 8. Re: SQL Query for IN,GroupBy and count(*)
                        user8680179
                        Thankx Purvesh


                        1.there should be minimum one row present in both date intervals for any staff number.
                        if i add one more insertion command like :
                        INSERT INTO EMP(STAFFNO, PRESENT_DATE, STATUS, DURATIONINHOUR) VALUES (3, to_date('12/07/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'PRESENT', 9);
                        Then it is satisfying the conditon count(*)>=3 but there is no row present in date interval2{Dec,12 and Dec,14}

                        so it is voilating the condition of 'minimum one row should be present in both date interval'...{"I need all those 'STAFFNO' whose status is 'PRESENT' in DateInterval1 as well as in DateInterval2"}

                        After adding above insertion command still the output shall be same
                        OUTPUT Shall be
                        STAFFNO,SUM(DURATIONINHOUR)=(1,27)

                        and staff number 3 shall not be displayed in output.
                        • 9. Re: SQL Query for IN,GroupBy and count(*)
                          chris227
                          Why not take notice of my suggestion.
                          with
                          EMP(STAFFNO, PRESENT_DATE, STATUS, DURATIONINHOUR) as (
                          Select 1, to_date('12/05/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'PRESENT', 8 from dual union all
                          Select 1, to_date('12/06/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'PRESENT', 9 from dual union all
                          Select 1, to_date('12/08/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'NOT PRESENT', 9 from dual union all
                          Select 1, to_date('12/13/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'PRESENT', 10 from dual union all
                          Select 2, to_date('12/05/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'PRESENT', 10 from dual union all
                          Select 2, to_date('12/06/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'PRESENT', 10 from dual union all
                          Select 3, to_date('12/05/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'PRESENT', 8 from dual union all
                          Select 3, to_date('12/06/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'PRESENT', 9 from dual union all
                          select 3, to_date('12/07/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'PRESENT', 9 from dual
                          )
                          
                          select
                           staffno
                          ,SUM(
                            case status when 'PRESENT' then DURATIONINHOUR end
                           )
                          sum
                          from 
                          emp e
                          group by
                          staffno
                          ,
                          case
                          when present_date
                          between to_date('12/04/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS')
                              AND to_date('12/07/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS')
                          then 1
                          when present_date
                          between to_date('12/12/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS')
                              AND to_date('12/14/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS')
                          then 1
                          else 0
                          end
                          having (
                          sum(case
                          when present_date
                          between to_date('12/04/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS')
                              AND to_date('12/07/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS')
                          then 1
                          when present_date
                          between to_date('12/12/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS')
                              AND to_date('12/14/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS')
                          then 2
                          else 0
                          end) > count(*)
                          )
                          and
                          (select count(*) from emp where staffno = e.staffno) >= 3
                          
                          STAFFNO SUM 
                          1 27 
                          However > 3 is not what you have written in your first post, this was > 3:
                          "also minimum occurrence of staff no. should be grater than 3(*including both date interval*)."

                          Edited by: chris227 on 09.01.2013 02:20
                          Misread => Correction
                          • 10. Re: SQL Query for IN,GroupBy and count(*)
                            user8680179
                            Actually both date interval and count shall be dynamically passed to query,I am using jdbc connection in which all three variables shall be set and and sql query shall be formed, means date interval, count or database set nothing shall be hardcoded in query.

                            Both date interval shall be descrete means it shall not happen as DateInterval1{to_date('12/04/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS') and to_date('12/07/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS')} and DateInterval2 {to_date('12/05/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS') and to_date('12/06/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS')}....Means dateinterval2 shall not be subset of dateinterval1 and there is any overlapping of dates
                            means it shall also not happen as
                            DateInterval1{to_date('12/04/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS') and to_date('12/07/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS')} and DateInterval2 {to_date('12/05/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS') and to_date('12/12/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS')}
                            • 11. Re: SQL Query for IN,GroupBy and count(*)
                              user8680179
                              but still the code suggested by Purvesh is not correct, because 'or' is used in



                              present_date between to_date('12/04/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS') and to_date('12/07/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS')
                              or present_date between to_date('12/12/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS') and to_date('12/14/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS')
                              • 12. Re: SQL Query for IN,GroupBy and count(*)
                                chris227
                                something like this.
                                Ah, may be you was confused of the with clause.
                                It is not needed for the query, just representing the test data as i didnt create any table
                                select
                                 staffno
                                ,SUM(
                                  case status when 'PRESENT' then DURATIONINHOUR end
                                 )
                                sum
                                from 
                                emp e
                                group by
                                staffno
                                ,
                                case
                                when present_date
                                between to_date(:d1, 'MM/DD/YYYY HH24:MI:SS')
                                    AND to_date(:d2, 'MM/DD/YYYY HH24:MI:SS')
                                then 1
                                when present_date
                                between to_date(:d3, 'MM/DD/YYYY HH24:MI:SS')
                                    AND to_date(:d4, 'MM/DD/YYYY HH24:MI:SS')
                                then 1
                                else 0
                                end
                                having (
                                sum(case
                                when present_date
                                between to_date(:d1, 'MM/DD/YYYY HH24:MI:SS')
                                    AND to_date(:d2, 'MM/DD/YYYY HH24:MI:SS')
                                then 1
                                when present_date
                                between to_date(:d3, 'MM/DD/YYYY HH24:MI:SS')
                                    AND to_date(:d4, 'MM/DD/YYYY HH24:MI:SS')
                                then 2
                                else 0
                                end) > count(*)
                                )
                                and
                                (select count(*) from emp where staffno = e.staffno) >= :c1
                                Edited by: chris227 on 09.01.2013 02:26

                                Edited by: chris227 on 09.01.2013 02:26

                                Edited by: chris227 on 09.01.2013 02:31
                                • 13. Re: SQL Query for IN,GroupBy and count(*)
                                  user8680179
                                  Yeah i was confused of with clause
                                  • 14. Re: SQL Query for IN,GroupBy and count(*)
                                    Purvesh K
                                    user8680179 wrote:
                                    but still the code suggested by Purvesh is not correct, because 'or' is used in



                                    present_date between to_date('12/04/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS') and to_date('12/07/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS')
                                    or present_date between to_date('12/12/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS') and to_date('12/14/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS')
                                    Yes, I missed the fact that there could be 3 counts, all coming from a single Group range. See the modified version of query:
                                    select staffno,
                                           sum(sm) sum_durationhour
                                      from (
                                            select staffno, 
                                                   case
                                                        when present_date between to_date('12/04/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS') and to_date('12/07/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS')
                                                          then 'GRP1'
                                                        when present_date between to_date('12/12/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS') and to_date('12/14/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS')
                                                          then 'GRP2'
                                                      end grp,
                                                   count(*) cnt,
                                                   sum(durationinhour) sm
                                              from emp
                                             where (present_date between to_date('12/04/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS') and to_date('12/07/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS')
                                                or present_date between to_date('12/12/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS') and to_date('12/14/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS')
                                                   )
                                               and status = 'PRESENT'
                                             group by staffno, case
                                                        when present_date between to_date('12/04/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS') and to_date('12/07/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS')
                                                          then 'GRP1'
                                                        when present_date between to_date('12/12/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS') and to_date('12/14/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS')
                                                          then 'GRP2'
                                                      end
                                           )
                                     group by staffno
                                     having sum(cnt) >= 3 and count(distinct grp) = 2;
                                    Let me explain the logic for the query:
                                            select staffno, 
                                                   case
                                                        when present_date between to_date('12/04/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS') and to_date('12/07/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS')
                                                          then 'GRP1'
                                                        when present_date between to_date('12/12/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS') and to_date('12/14/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS')
                                                          then 'GRP2'
                                                      end grp,
                                                   count(*) cnt,
                                                   sum(durationinhour) sm
                                              from emp
                                             where (present_date between to_date('12/04/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS') and to_date('12/07/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS')
                                                or present_date between to_date('12/12/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS') and to_date('12/14/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS')
                                                   )
                                               and status = 'PRESENT'
                                             group by staffno, case
                                                        when present_date between to_date('12/04/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS') and to_date('12/07/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS')
                                                          then 'GRP1'
                                                        when present_date between to_date('12/12/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS') and to_date('12/14/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS')
                                                          then 'GRP2'
                                                      end
                                    Inline view, that will pick the records that are within the Ranges, as specified in First Post and having Status as Present.
                                    GRP column, is used to segregate the Groups for the Two date Ranges. If the Present_Date column falls in First Range, then it will return GRP1, If in Second range, then GRP2.
                                    CNT column is the count of rows for each of the groups.
                                    SM is summation of DURATIONHOUR column for each of Groups

                                    Grouping is on STAFFNO and the GROUP basis (Group is date falling in one of the Two ranges).
                                    having sum(cnt) >= 3 and count(distinct grp) = 2;
                                    present in the Outer Query, ensures you have at least 3 occurances of STAFFNO, and Data coming from Both the groups.

                                    I hope that should resolve your concerns.
                                    1 2 Previous Next