Niveau 5 : Résumer une table : les agrégats

Leçon

Jusqu'ici, chaque ligne de la table donnait une ligne de résultat. Une fonction d'agrégat fait l'inverse : elle résume plusieurs lignes en une seule valeur. COUNT compte, SUM additionne, AVG calcule la moyenne, MIN et MAX donnent la plus petite et la plus grande valeur.

Sans autre précision, l'agrégat porte sur toute la table et le résultat tient en une seule ligne. SELECT COUNT(*), AVG(rating) FROM movies répond à deux questions d'un coup : combien de films, et quelle note moyenne.

COUNT a deux formes. COUNT(*) compte toutes les lignes. COUNT(colonne) ne compte que les lignes où la colonne est remplie : sur les films, COUNT(*) vaut 10 mais COUNT(director_id) vaut 9, car Deep Current n'a pas de réalisateur. Les autres agrégats ignorent aussi les NULL : AVG fait la moyenne des seules valeurs connues.

MIN et MAX fonctionnent aussi sur les textes (ordre alphabétique) et sur les dates (la plus ancienne, la plus récente).

Le WHERE s'applique avant l'agrégat : on choisit d'abord les lignes, puis on les résume. SELECT AVG(grade) FROM grades WHERE subject = 'math' donne la moyenne des seules notes de maths. Si aucune ligne ne passe le filtre, COUNT renvoie 0, mais SUM, AVG, MIN et MAX renvoient NULL.

ROUND(valeur, n) arrondit à n décimales : ROUND(AVG(grade), 1). Une moyenne a souvent beaucoup de décimales : arrondis-la à l'affichage.

Une limite importante : on ne peut pas afficher une colonne ordinaire à côté d'un agrégat sans regroupement. SELECT title, MAX(rating) n'a pas de sens en SQL standard : MAX donne une seule valeur, mais quel titre afficher à côté ? Le niveau 14 montre la bonne méthode.

Syntaxe

SELECT COUNT(*) AS nb, AVG(colonne) AS moyenne, MAX(colonne) AS maxi
FROM ma_table
WHERE …;

Exemple commenté

SELECT COUNT(*) AS nb_movies,
       AVG(rating) AS avg_rating,
       MAX(duration) AS longest
FROM movies;

Toute la table est résumée en une seule ligne : 10 films, leur note moyenne et la durée du plus long.

Résultat de l’exemple

À retenir

• Un agrégat sans GROUP BY renvoie une seule ligne.
• COUNT(*) compte les lignes, COUNT(colonne) les valeurs non vides.
• WHERE filtre avant le calcul.

Pièges fréquents

• Mélanger une colonne simple et un agrégat sans regroupement (SELECT title, MAX(rating)) est une erreur en SQL standard, même si SQLite l'accepte.
• AVG sur une colonne qui contient des NULL fait la moyenne des seules valeurs connues.

Spécificité SQLite

SQLite accepte SELECT title, MAX(rating) FROM movies et renvoie le titre du film le mieux noté : c'est une « colonne nue », une particularité de SQLite. PostgreSQL, SQL Server ou Oracle refusent cette requête. La méthode portable (une sous-requête) arrive au niveau 14.

Les 5 exercices du niveau

1. Guidé · Combien y a-t-il de livres dans la table books ? (Table : books)
2. Entraînement · Combien de places ont été vendues au total, tous vols confondus ? (Table : flights)
3. Entraînement · Affiche la date du premier prêt (first_loan) et celle du dernier prêt (last_loan). (Table : loans)
4. Entraînement · Combien de contacts ont une adresse e-mail renseignée ? (Table : contacts)
5. Défi · Pour les notes de mathématiques, affiche en une seule ligne : le nombre de notes (nb_grades), leur moyenne arrondie à une décimale (avg_math) et l'écart entre la meilleure et la moins bonne note (spread). (Table : grades)

Dans SpeedQL, chaque requête est corrigée tout de suite : le résultat est comparé à celui attendu, puis la requête est relancée sur une base de contrôle cachée. Chaque exercice a ses indices écrits, à afficher seulement si tu bloques.

Voir aussi : la fiche de l’aide-mémoire · les exercices SQL corrigés sur cette notion