**GROUP BY clause**

This clause creates subsets (groups of rows) where the value of one (or more) column is the same. This clause is associated with the use of statistical functions which will be applied to the subsets defined by the GROUP BY clause.

*-Group per rank of teachers.*

**SELECT **Grade**FROM** ENSEIGNANT**GROUP BY **Grade;

This request will create as many sub-sets of Grade attribute values as possible. Here 3 subsets : ASS, MCF, PR.

**HAVING clause**

This clause, unlike the WHERE clause that specifies the conditions to be applied to the rows of a table, specifies conditions at the level of the subsets created by GROUP BY.

*-Subset per rank of 'ASS' teachers*

**SELECT **Grade**FROM** ENSEIGNANT**GROUP BY **Grade**HAVING **Grade='ASS';

**Statistical functions**

Functions |
Meaning |

COUNT ([DISTINCT] expr [alias] |
Returns the number of values in the column. If DISTINCT is specified, redundant values are discarded. |

COUNT (*) |
Count all rows of the table. |

AVG (expr [alias]) |
Returns the average value of the given column or expression. |

MIN (expr [alias]) |
Returns the lowest value in the given column. |

MAX (expr [alias]) |
Returns the highest value in the given column. |

SUM (expr [alias]) |
Returns the sum of the values of the given column. |

**-Displays the birthday of the youngest and oldest students.**

**SELECT MIN**(Dtnaiss) **AS** "Younger", **MAX**(Dtnaiss) **AS** "Older"**FROM** ETUDIANT;

**-Displays the overall number of students.**

**SELECT COUNT**(*)**FROM **ETUDIANT;

**Test queries examples**

**Statistical functions and GROUP BY clause**

Statistical functions work with a set of data and return only one value. In general, if an attribute is used in a SELECT clause, the result can contain between zero and m values. This may result in conflicts when using aggregate functions that return only one value.

The following SELECT command, intended to compute the mean score of the student, is therefore incorrect and generates the execution error: "Invalid column reference"

**SELECT **Numetu, **AVG**(note)**FROM **NOTES;

The proper formulation is:

**SELECT **Numetu, **AVG**(note)**FROM **NOTES**GROUP BY **Numetu;

In such a case, a subset will be created for each number of students and the average will be computed at the subset level.

**Test queries examples**

**Q07: Display for each student, identified by his name, the total number of marks he obtained.**

**Q08: Indicate which students have less than 4 marks.**

**Q09: Show the name of the first and last student alphabetically.**

**Q10: Display for each subject number the number of students with a mark.**