# SQL: Groups of rows

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

FROM ENSEIGNANT

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

FROM ENSEIGNANT

##### Statistical functions

-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

SQL summary

##### 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

SQL summary

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

SQL summary

Q08: Indicate which students have less than 4 marks.

SQL summary

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

SQL summary

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