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.