Condition on a group of tuples  

  • Example of a group condition:   size of group ≥ 2

    A group condition clause will filter entire groups of tuples

  Specifying a group condition in SQL  

  • A group condition is specified using the HAVING clause following the GROUP BY clause: 

       SELECT    grouping-attributes or set-functions  
   FROM      relation-list		     
   WHERE     tuple-boolean-condition	     
   GROUP BY  grouping-attribute-list
   HAVING    group-boolean-condition        <---- Group condition

  • The group condition typically contains a set function: 

       SELECT    grouping-attributes or set-functions  
   FROM      relation-list		     
   WHERE     tuple-boolean-condition	     
   GROUP BY  grouping-attribute-list
   HAVING    SetFunction( .. ) RelOp ...     <---- Group condition

  Example of a group condition in SQL  

  • Sample employee relation: 

          fname     lname     dno 
     --------   --------- ----- 
      James     Borg       1 
      Alicia    Zelaya     4 
      Jennif    Wallace    4 
      Ahmad     Jabbar     4 
      John      Smith      5 
      Frankl    Wong       5 
      Ramesh    Narayan    5 
      Joyce     English    5

  • We can form groups based on common dno values: 

          select    dno, count(*)           +-----+----------+
      from      employee	        | dno | count(*) |
      group by  dno                     +-----+----------+
                                        |   1 |        1 |
					|   4 |        3 |
					|   5 |        4 |
					+-----+----------+

  Example of a group condition in SQL  

  • Sample employee relation: 

          fname     lname     dno 
     --------   --------- ----- 
      James     Borg       1 
      Alicia    Zelaya     4 
      Jennif    Wallace    4 
      Ahmad     Jabbar     4 
      John      Smith      5 
      Frankl    Wong       5 
      Ramesh    Narayan    5 
      Joyce     English    5

  • We can use a group condition to remove some groups: 

          select    dno, count(*)           +-----+----------+
      from      employee	        | dno | count(*) |
      group by  dno                     +-----+----------+
      having    count(*) > 2            |   4 |        3 |
					|   5 |        4 |
					+-----+----------+