CS457 Syllabus & Progress

## 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 total 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, sum(salary) FROM employee GROUP BY dno DNO SUM(SALARY) ---------- ----------- 1 55000 4 93000 5 133000 ```

• 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 is not a the GROUP BY attribute (and we cannot show a unique value for each group in one row of output)

• 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...