---

Forming groups with common attribute values: Grouping Attributes

---

• Forming groups by attribute values

  • Recall that you can group a set a tuples by one or more attribute values

    ---

    ---

  • Example: grouping employee tuples by their dno value:

    ---

    ---

    ---

  • Example: grouping employee tuples by their dno and sex value:

    ---

  • The attribute used to group the tuples are called Grouping Attributes

    ---

  • The SQL construct to form groups of tuples based on attribute values is:

    SELECT ... FROM relation-list WHERE tuple-boolean-condition GROUP BY grouping-attributes

    ---

    ---

    ---

    ---

    ---

  • Example:

    For each department, find the average salary paid to employees of that department

  • Solution:

    Here is the employee relation: SELECT fname, lname, sex, dno, salary FROM employee fname lname sex dno salary ------ -------- ------ ----------- --------- James Borg M 1 55000.00 Alicia Zelaya F 4 25000.00 Jennif Wallace F 4 43000.00 Ahmad Jabbar M 4 25000.00 John Smith M 5 30000.00 Frankl Wong M 5 40000.00 Ramesh Narayan M 5 38000.00 Joyce English F 5 25000.00 Here is the solution: SELECT dno, avg(salary) FROM employee GROUP BY dno +-----+--------------+ | dno | avg(salary) | +-----+--------------+ | 1 | 55000.000000 | | 4 | 31000.000000 | | 5 | 33250.000000 | +-----+--------------+

  ---

  ---

  ---

  ---

• Grouping with multiple common attribute values

  • Query:

    Find the total salary paid to male and female employees (seperate total) for each department

  • Solution:

    Employee relation: +--------+---------+-----+------+----------+ | fname | lname | dno | sex | salary | +--------+---------+-----+------+----------+ | James | Borg | 1 | M | 55000.00 | | Alicia | Zelaya | 4 | F | 25000.00 | | Jennif | Wallace | 4 | F | 43000.00 | | Ahmad | Jabbar | 4 | M | 25000.00 | | Joyce | English | 5 | F | 25000.00 | | John | Smith | 5 | M | 30000.00 | | Frankl | Wong | 5 | M | 40000.00 | | Ramesh | Narayan | 5 | M | 38000.00 | +--------+---------+-----+------+----------+ I highlighted the groups formed using (dno, sex) values SELECT dno, sex, sum(salary) FROM employee GROUP BY dno, sex +-----+------+-------------+ | dno | sex | sum(salary) | +-----+------+-------------+ | 1 | M | 55000.00 | | 4 | F | 68000.00 | | 4 | M | 25000.00 | | 5 | F | 25000.00 | | 5 | M | 108000.00 | +-----+------+-------------+

  ---

  ---

  ---

  ---

• Restriction on the SELECT attribute list when using GROUP BY
  • Restriction:

    The attributes that you can specify in the SELECT attribute list must be a subset of the attributes in the GROUP BY clause In other words: SELECT subset of {A, B, C, D}, setFunc1(..), setFunc2(..), ... FROM ... WHERE ... GROUP BY A, B, C, D Reason: When you form groups based on the GROUP BY attributes, then: All members in a group will have the same value for the GROUP BY attribute (and we can show one value for each group) This is not true for attributes that are not a GROUP BY attribute (Therefore: we cannot output a unique value for the group of tuple)

    ---

    ---

  • Note:

    Some SQL implementations do not enforce the restriction (stated in the SQL standard) In fact: MySQL does not enforce the group by attribute list restriction !!!

  ---

  ---

  ---

  ---

• Note on output format...
  • NOTE:

    You don't need to specify all grouping attributes in the SELECT clause However, if you omit some grouping attributes, you will have no idea what the result mean !!!

  • Example:

    SELECT sum(salary) /* Omit dno, sex */ FROM employee GROUP BY dno, sex SUM(SALARY) ----------- 25000 55000 108000 68000 25000

    You cannot tell what is the meaning of the output values...