---

Condition on a group (of tuples): the having clause

---

• Group condition: a condition on a group of tuples

  • Recall that the GROUP BY clause can form groups based on attribute values on the tuples.

    Example: group the employee tuples based on their values for the sex attribute:

  • After forming groups based on their attribute values, you can also specify a condition on each group

    Example: suppose we only want to consider groups where the size of the group is at least 2:

    Group condition:

    A group condition is a condition that applies to a group formed by the GROUP BY clause A group condition will filter out (remove) (entire) groups that does not satisfy the group condition

    ---

    ---

    ---

    ---

  • Specifying a group condition in SQL:

    The 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

    Note:

    The above syntax structure is the structure of the most general SQL command (It has every possible clause of an SQL command included in the syntax.....)

  ---

  ---

  ---

  ---

• The group condition of the HAVING clause
  • Syntax of of a group condition:

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

    RelOp is a a relational operator (such as <, ≤, > ≥, =, ≠)

    ---

    ---

  • The HAVING group condition:

    The group (boolean) condition (specified using the HAVING clause) is a condition on a group of tuples

    ---

    ---

    ---

    ---

  • Example:

    The 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 | +--------+---------+-----+ For groups based on dno ; and count the number of employees in each group: select dno, count(*) from employee group by dno +-----+----------+ | dno | count(*) | +-----+----------+ | 1 | 1 | | 4 | 3 | | 5 | 4 | +-----+----------+ Retrieve groups that has >= 2 elements: select dno, count(*) from employee group by dno having count(*) > 2 +-----+----------+ | dno | count(*) | +-----+----------+ | 4 | 3 | | 5 | 4 | +-----+----------+