03_ Opérations entre les enregistrements

%% nav-debut : généré par gen_navigation.py, ne pas modifier à la main %% > [!nav] > ↩ [[Code et Développement/Développement d'Applications/SQL I/SQL I|Accueil · SQL I]] > > | [[Code et Développement/Développement d'Applications/SQL I/02_ Requêtes sur une seule table\|← Requêtes sur une seule table]] | [[Code et Développement/Développement d'Applications/SQL I/04_Requêtes sur 2 tables\|Requêtes sur 2 tables →]] | > |:---|---:| %% nav-fin %% [[Code et Développement/Développement d'Applications/SQL I/02_ Requêtes sur une seule table|Requêtes sur une seule table]] # Opérations entre les enregistrements - Les fonctions d'agrégat ## Introduction Jusqu'à présent, vous avez appris à interroger et manipuler des données enregistrement par enregistrement, en sélectionnant des champs spécifiques, en filtrant des enregistrements avec `WHERE`, et en ordonnant les résultats. Ces compétences sont fondamentales, mais le véritable pouvoir de SQL réside souvent dans sa capacité à **synthétiser et analyser des ensembles de données**. Dans le monde réel, il est rare de vouloir uniquement consulter des enregistrements individuels. Vous aurez plutôt besoin de savoir : * "Combien d'étudiants y a-t-il dans chaque département ?" * "Quelle est la note moyenne des étudiants inscrits cette année ?" * "Quel est le budget total alloué à tous les projets ?" Pour répondre à ce type de questions, SQL propose des **fonctions d'agrégat** et la clause `GROUP BY`. Ce chapitre vous guidera à travers ces concepts essentiels, vous permettant de passer d'une vision individuelle à une vision agrégée de vos données. > [!note] Prérequis > Ce chapitre suppose que vous maîtrisez les bases de SQL, notamment les clauses `SELECT`, `FROM`, `WHERE` et `ORDER BY`. --- ## 1. Introduction aux opérations d'agrégat Les opérations d'agrégat sont des fonctions qui opèrent sur un **ensemble d'enregistrements** (un groupe, ou la totalité des enregistrements restant après le `FROM` et le `WHERE`) et retournent **une seule valeur**. Elles sont indispensables pour obtenir des statistiques, des totaux, des moyennes et d'autres indicateurs synthétiques à partir de vos données brutes. > [!definition] Fonction d'agrégat > Une fonction d'agrégat, ou fonction d'agrégation, est une fonction SQL qui prend en entrée une collection de valeurs (souvent toutes les valeurs d'un champ pour un groupe d'enregistrements) et retourne une seule valeur qui résume ou calcule une propriété de cette collection. --- ## 2. La clause `GROUP BY` : regrouper les données La clause `GROUP BY` est la pierre angulaire des opérations d'agrégat. Elle permet de diviser l'ensemble des enregistrements d'une table (ou le résultat d'une clause `FROM`/`WHERE`) en groupes, afin d'appliquer une fonction d'agrégat à chaque groupe indépendamment. ### 2.1. Concept Imaginez que vous avez une table d'étudiants avec leur département et leur note moyenne. Si vous voulez connaître la note moyenne *par département*, vous devez d'abord regrouper tous les étudiants appartenant au même département, puis calculer la moyenne pour chaque groupe. `GROUP BY` va permettre ce regroupement. > [!note] Principe de fonctionnement > 1. Les enregistrements sont sélectionnés (après `FROM` et `WHERE`). > 2. Ces enregistrements sont ensuite regroupés en fonction des valeurs d'un ou plusieurs champs spécifiés dans la clause `GROUP BY`. > 3. Pour chaque groupe ainsi formé, les fonctions d'agrégat sont appliquées. ### 2.2. Syntaxe La clause `GROUP BY` est utilisée conjointement avec `SELECT` et les fonctions d'agrégat. ```sql SELECT champ_groupement_1, [champ_groupement_2, ...], fonction_agregat(expression_1), [fonction_agregat(expression_2), ...] FROM nom_table [WHERE condition_filtre_enregistrements] GROUP BY champ_groupement_1 [, champ_groupement_2, ...]; ``` > [!warning] Règle fondamentale de `GROUP BY` > **Tout champ présent dans la clause `SELECT` qui n'a PAS de fonction d'agrégat associée DOIT impérativement figurer dans la clause `GROUP BY`.** > > Si vous sélectionnez un champ non agrégé sans le spécifier dans `GROUP BY`, le SGBD ne saurait pas quelle valeur afficher pour ce champ, car il y aurait potentiellement plusieurs valeurs pour un même groupe. ### 2.3. Exemple simple Considérons une table `Etudiants` avec les champs `id_etudiant`, `nom`, `prenom`, `id_departement`, `note_moyenne` et `credits_obtenus`. La moyenne de Paul Leroy n'a pas encore été saisie : son champ `note_moyenne` est vide (`NULL`). | id_etudiant | nom | prenom | id_departement | note_moyenne | credits_obtenus | | :---------- | :------ | :----- | :------------- | :----------- | :-------------- | | 1 | Dupont | Jean | 101 | 14.5 | 60 | | 2 | Martin | Sophie | 102 | 16.0 | 54 | | 3 | Durand | Pierre | 101 | 12.0 | 48 | | 4 | Petit | Alice | 103 | 15.5 | 60 | | 5 | Leroy | Paul | 102 | NULL | 30 | | 6 | Bernard | Marie | 101 | 17.0 | 60 | | 7 | Robert | Julie | 103 | 11.5 | 42 | **Question :** Compter le nombre d'étudiants par département. ```sql SELECT id_departement, COUNT(id_etudiant) AS nombre_etudiants FROM Etudiants GROUP BY id_departement; ``` **Exécution de la requête :** 1. Après le `FROM`, comme celui-ci ne contient que la table `Etudiants`, nous obtenons cette table : | id_etudiant | nom | prenom | id_departement | note_moyenne | credits_obtenus | | :---------- | :------ | :----- | :------------- | :----------- | :-------------- | | 1 | Dupont | Jean | 101 | 14.5 | 60 | | 2 | Martin | Sophie | 102 | 16.0 | 54 | | 3 | Durand | Pierre | 101 | 12.0 | 48 | | 4 | Petit | Alice | 103 | 15.5 | 60 | | 5 | Leroy | Paul | 102 | NULL | 30 | | 6 | Bernard | Marie | 101 | 17.0 | 60 | | 7 | Robert | Julie | 103 | 11.5 | 42 | *Ici, un enregistrement est un étudiant de la table `Etudiants`.* 2. Après le `GROUP BY`, nous avons regroupé les enregistrements ayant le même numéro de département, pour ne former qu'un seul enregistrement par département. Cela donne : | id_etudiant | nom | prenom | id_departement | note_moyenne | credits_obtenus | | :---------- | :-------------------------- | :---------------------- | :------------- | :------------------- | :-------------- | | 1 3 6 | Dupont Durand Bernard | Jean Pierre Marie | 101 | 14.5 12.0 17.0 | 60 48 60 | | 2 5 | Martin Leroy | Sophie Paul | 102 | 16.0 NULL | 54 30 | | 4 7 | Petit Robert | Alice Julie | 103 | 15.5 11.5 | 60 42 | Vous remarquez que les champs n'étant pas dans le `GROUP BY` ont toutes les valeurs des enregistrements regroupés. >[!important] >Si nous appelons un de ces champs dans le `SELECT` sans lui associer une opération d'agrégat, la requête retournera, selon le SGBD utilisé, une erreur (ne sachant pas quelle valeur retourner) ou la première valeur trouvée (ce qui est encore plus dangereux). >Cela explique la règle fondamentale vue au point [[#2.2. Syntaxe]]. > [!warning] Ce que représente un enregistrement change ! > Avec le regroupement, un enregistrement n'est plus un étudiant de la table `Etudiants` mais un numéro de département et donc, par extension, **un département**. 3. Après le `SELECT`, nous obtenons le résultat final : | id_departement | nombre_etudiants | | :------------- | :--------------- | | 101 | 3 | | 102 | 2 | | 103 | 2 | Dans l'opération d'agrégat `COUNT`, nous comptons le nombre de valeurs présentes dans le champ `id_etudiant`. Il n'y a donc plus qu'une seule valeur à afficher. *Un enregistrement correspond toujours à un département pour lequel nous avons le nombre d'étudiants.* >[!important] Pourquoi `COUNT(id_etudiant)` donne-t-il le nombre d'étudiants ? > >L'erreur serait de se baser sur le nom du champ ! > >Voici le raisonnement à tenir : >1. L'opération compte, pour chaque groupe, le nombre de valeurs trouvées dans le champ `id_etudiant`. >2. Chaque groupe rassemble les enregistrements ayant le même numéro de département. >3. Au départ (après le `FROM`), un enregistrement représente un étudiant : compter des enregistrements revient donc à compter des étudiants. >4. Comme `id_etudiant` est l'identifiant de la table (clé primaire), il a forcément une valeur : on est certain de compter tous les enregistrements, donc tous les étudiants de chaque département. > > - Si vous n'avez pas réussi à tout comprendre, pas d'inquiétude : cela est réexpliqué juste après et la pratique d'exercices vous permettra de mieux assimiler ce raisonnement. > - Si vous pensez avoir compris, essayez d'expliquer ce que nous aurions compté si nous avions mis `COUNT(note_moyenne)` à la place. (La réponse est dans la partie 3.1.2 😉) ### 2.4. Regroupement sur plusieurs champs Vous pouvez regrouper les données sur plusieurs champs. Chaque combinaison unique des valeurs de ces champs formera un groupe distinct. > [!example] Exemple > Supposons que la table `Etudiants` possède aussi un champ `annee_etude` (1re, 2e, 3e année…). > ```sql > SELECT id_departement, annee_etude, COUNT(id_etudiant) AS nombre_etudiants > FROM Etudiants > GROUP BY id_departement, annee_etude; > ``` > Cette requête forme un groupe par **couple** département / année : par exemple, un groupe pour les étudiants de 1re année du département 101, un autre pour ceux de 2e année du département 101, etc. > > Un enregistrement du résultat correspond donc à **une année d'étude dans un département**, avec son nombre d'étudiants. ### 2.5. Gestion des valeurs `NULL` dans `GROUP BY` Les valeurs `NULL` dans le champ de groupement sont traitées comme une catégorie distincte. Tous les enregistrements ayant `NULL` pour le champ de groupement seront regroupés ensemble dans un seul groupe `NULL`. --- ## 3. Les fonctions d'agrégat standard Il existe plusieurs fonctions d'agrégat standard. Elles sont essentielles pour toute analyse de données. ### 3.1. `COUNT()` : compter La fonction `COUNT()` est utilisée pour ***compter des enregistrements ou des valeurs***, selon ce qu'on lui donne entre parenthèses. #### 3.1.1. `COUNT(*)` Compte le nombre total d'enregistrements dans un groupe, y compris les enregistrements contenant des valeurs `NULL`. ```sql SELECT COUNT(*) AS nb_etudiants FROM Etudiants; ``` | nb_etudiants | | :----------- | | 7 | Cette requête compte le nombre d'enregistrements présents dans la table `Etudiants`. Par définition, un enregistrement de cette table est un étudiant, donc nous comptons le nombre d'étudiants. #### 3.1.2. `COUNT(nom_champ)` Compte le nombre de valeurs **non `NULL`** pour le champ spécifié. ```sql SELECT COUNT(note_moyenne) AS etudiants_avec_moyenne FROM Etudiants; ``` | etudiants_avec_moyenne | | :--------------------- | | 6 | Cette requête compte le nombre d'enregistrements présents dans la table `Etudiants` **pour lesquels le champ `note_moyenne` contient une valeur non `NULL`**. Par définition, un enregistrement de cette table est un étudiant, donc nous comptons le nombre d'étudiants dont on connaît la moyenne. Paul Leroy, dont la moyenne est `NULL`, n'est pas compté : le résultat est 6 et non 7. > [!example] Exemple avec `COUNT()` et `GROUP BY` > **Question :** Pour chaque département, compter le nombre total d'étudiants et le nombre d'étudiants ayant une note moyenne renseignée. > > ```sql > SELECT > id_departement, > COUNT(*) AS total_etudiants, > COUNT(note_moyenne) AS etudiants_avec_note > FROM > Etudiants > GROUP BY > id_departement; > ``` > > | id_departement | total_etudiants | etudiants_avec_note | > | :------------- | :-------------- | :------------------ | > | 101 | 3 | 3 | > | 102 | 2 | 1 | > | 103 | 2 | 2 | > > C'est la réponse à la question de la partie 2.3 : `COUNT(note_moyenne)` compte, pour chaque département, **les étudiants dont la moyenne est connue**. Dans le département 102, Paul Leroy n'a pas de moyenne : il est compté par `COUNT(*)` mais pas par `COUNT(note_moyenne)`. ### 3.2. `SUM()` : calculer la somme La fonction `SUM()` calcule la somme de toutes les valeurs (non `NULL`) d'un champ numérique dans un groupe. > [!warning] Type de données > `SUM()` ne peut être appliquée qu'à des champs de type numérique. ```sql SELECT id_departement, SUM(credits_obtenus) AS total_credits_departement FROM Etudiants GROUP BY id_departement; ``` | id_departement | total_credits_departement | | :------------- | :------------------------ | | 101 | 168 | | 102 | 84 | | 103 | 102 | >[!important] `SUM()` et `NULL` >Si certaines valeurs sont `NULL`, elles sont ignorées. >Si **toutes** les valeurs sont `NULL`, la fonction `SUM()` retourne `NULL`. ### 3.3. `AVG()` : calculer la moyenne La fonction `AVG()` calcule la moyenne arithmétique de toutes les valeurs (non `NULL`) d'un champ numérique dans un groupe. > [!note] Formule de la moyenne > La moyenne est calculée comme suit : > $ \bar{x} = \frac{\sum_{i=1}^{n} x_i}{n} $ > où $x_i$ sont les valeurs non `NULL` et $n$ est le nombre de ces valeurs. ```sql SELECT id_departement, AVG(note_moyenne) AS moyenne_notes_departement FROM Etudiants GROUP BY id_departement; ``` | id_departement | moyenne_notes_departement | | :------------- | :------------------------ | | 101 | 14.5 | | 102 | 16.0 | | 103 | 13.5 | Pour le département 102, la moyenne est 16.0 et non 8.0 : la valeur `NULL` de Paul Leroy est ignorée, la moyenne est donc calculée sur une seule note. > [!tip] Nombre de décimales > Le résultat de `AVG()` peut s'afficher avec plusieurs décimales (par exemple `14.50000`). Pour l'arrondir, utilisez la fonction `ROUND()` (voir [[Code et Développement/Développement d'Applications/SQL I/07_Fonctions utiles#Les Fonctions Numériques|Fonctions utiles]]). ### 3.4. `MIN()` : trouver la valeur minimale La fonction `MIN()` retourne la valeur minimale (non `NULL`) d'un champ dans un groupe. Elle peut être appliquée à des nombres, à des chaînes de caractères (ordre alphabétique) et à des dates (la date la plus ancienne). ```sql SELECT id_departement, MIN(note_moyenne) AS note_min_departement FROM Etudiants GROUP BY id_departement; ``` | id_departement | note_min_departement | | :------------- | :------------------- | | 101 | 12.0 | | 102 | 16.0 | | 103 | 11.5 | ### 3.5. `MAX()` : trouver la valeur maximale La fonction `MAX()` retourne la valeur maximale (non `NULL`) d'un champ dans un groupe. Elle peut être appliquée à des nombres, à des chaînes de caractères (ordre alphabétique) et à des dates (la date la plus récente). ```sql SELECT id_departement, MAX(note_moyenne) AS note_max_departement FROM Etudiants GROUP BY id_departement; ``` | id_departement | note_max_departement | | :------------- | :------------------- | | 101 | 17.0 | | 102 | 16.0 | | 103 | 15.5 | > [!tip] Comportement des `NULL` > Toutes les fonctions d'agrégat (`COUNT(champ)`, `SUM`, `AVG`, `MIN`, `MAX`) **ignorent les valeurs `NULL`**. Seul `COUNT(*)` compte tous les enregistrements, qu'ils contiennent des `NULL` ou non. --- ## 4. La clause `HAVING` : filtrer les groupes Une fois que les enregistrements ont été regroupés et les agrégats calculés, vous pourriez vouloir filtrer ces groupes en fonction des valeurs agrégées. C'est le rôle de la clause `HAVING`. ### 4.1. Rappel : `WHERE` ou `HAVING`, une distinction cruciale C'est l'une des confusions les plus courantes pour les débutants. > [!definition] Différence `WHERE` et `HAVING` > * La clause `WHERE` filtre les **enregistrements** *avant* qu'ils ne soient regroupés. Elle ne peut pas utiliser de fonctions d'agrégat. > * La clause `HAVING` filtre les **groupes** *après* que les fonctions d'agrégat ont été appliquées. Elle utilise des fonctions d'agrégat. ### 4.2. Syntaxe ```sql SELECT champ_groupement, fonction_agregat(expression) FROM nom_table [WHERE condition_filtre_enregistrements] GROUP BY champ_groupement HAVING condition_filtre_groupes; -- Cette condition inclut des fonctions d'agrégat ``` ### 4.3. Exemple **Question :** Afficher les départements qui ont plus de 2 étudiants. ```sql SELECT id_departement, COUNT(id_etudiant) AS nombre_etudiants FROM Etudiants GROUP BY id_departement HAVING COUNT(id_etudiant) > 2; ``` > [!example] Résultat de l'exemple > | id_departement | nombre_etudiants | > | :------------- | :--------------- | > | 101 | 3 | ### 4.4. Exemple combinant `WHERE` et `HAVING` **Question :** Afficher les départements qui ont au moins 2 étudiants ayant une moyenne supérieure ou égale à 13. ```sql SELECT id_departement, COUNT(id_etudiant) AS nombre_etudiants FROM Etudiants WHERE note_moyenne >= 13 GROUP BY id_departement HAVING COUNT(id_etudiant) >= 2; ``` **Exécution de la requête :** 1. Après le `FROM`, nous retrouvons les 7 étudiants de la table `Etudiants`. 2. Le `WHERE` filtre les **étudiants** : ceux dont la moyenne n'est pas supérieure ou égale à 13 sont mis en rouge et disparaissent. Paul Leroy disparaît aussi : sa moyenne est `NULL`, la condition `note_moyenne >= 13` n'est donc pas vraie. | id_etudiant | nom | prenom | id_departement | note_moyenne | credits_obtenus | | :---------- | :------ | :----- | :------------- | :----------- | :-------------- | | 1 | Dupont | Jean | 101 | 14.5 | 60 | | 2 | Martin | Sophie | 102 | 16.0 | 54 | | 3 | Durand | Pierre | 101 | 12.0 | 48 | | 4 | Petit | Alice | 103 | 15.5 | 60 | | 5 | Leroy | Paul | 102 | NULL | 30 | | 6 | Bernard | Marie | 101 | 17.0 | 60 | | 7 | Robert | Julie | 103 | 11.5 | 42 | *Ici, un enregistrement est un étudiant ayant une moyenne supérieure ou égale à 13.* 3. Le `GROUP BY` regroupe les étudiants restants par département, et `COUNT(id_etudiant)` compte les étudiants de chaque groupe : | id_departement | COUNT(id_etudiant) | | :------------- | :----------------- | | 101 | 2 | | 102 | 1 | | 103 | 1 | *Un enregistrement est maintenant un département, avec son nombre d'étudiants ayant au moins 13 de moyenne.* 4. Le `HAVING` filtre les **départements** : seuls ceux qui ont au moins 2 étudiants sont conservés. | id_departement | COUNT(id_etudiant) | | :------------- | :----------------- | | 101 | 2 | | 102 | 1 | | 103 | 1 | 5. Après le `SELECT`, nous obtenons le résultat final : | id_departement | nombre_etudiants | | :------------- | :--------------- | | 101 | 2 | > [!tip] À retenir > Le `WHERE` agit sur les **étudiants** (avant le regroupement), le `HAVING` agit sur les **départements** (après le regroupement). --- Nous pouvons attaquer les requêtes sur 2 tables : [[Code et Développement/Développement d'Applications/SQL I/04_Requêtes sur 2 tables|Requêtes sur 2 tables]] %% nav-debut : généré par gen_navigation.py, ne pas modifier à la main %% > [!nav] > ↩ [[Code et Développement/Développement d'Applications/SQL I/SQL I|Accueil · SQL I]] > > | [[Code et Développement/Développement d'Applications/SQL I/02_ Requêtes sur une seule table\|← Requêtes sur une seule table]] | [[Code et Développement/Développement d'Applications/SQL I/04_Requêtes sur 2 tables\|Requêtes sur 2 tables →]] | > |:---|---:| %% nav-fin %% --- 🗓️ Historique Dernière mise à jour : `29 octobre 2026` Rédigé par : [[Vincent TELLIER]]