**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, when an attribute is selected within a SELECT clause, the output can contain values from zero to m. This can lead to conflicts when you use aggregate functions that return a single value.

The following SELECT command, which is intended to calculate the average student score, is therefore incorrect and will generate the characteristic runtime 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 mean 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.**