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.

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

SQL summary

Statistical functions and GROUP BY clause

Statistical functions work with a set of data and return only one value. Generally, when an attribute is selected in a SELECT clause, the result may contain between zero and m values. 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

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.

Next section | Previous section | SQL summary