Licence L2 S4 2020/2021
Laboratoire
SQL MDL : Interrogation des
données (2)
20‐mars‐22 Les Bases de Données H. Meziane 1
Groupement
SQL traite à la fois des regroupements de lignes (agrégats) et des fonctions de
groupe. Nous étudierons les parties en gras de l’instruction SELECT suivante :
SELECT [ DISTINCT | ALL ] listeColonnes
FROM nomTable1 [,nomTable2]...
[ WHERE condition ]
[ clause Regroupement ]
[ HAVING condition ]
[ clause Ordonnancement ];
ListeColonnes : peut inclure des expressions (présentes dans la clause de
regroupement) ou des fonctions de groupe.
ClauseRegroupement : GROUP BY (expression1 [,expression2]...) permet
de regrouper des lignes selon la valeur des expressions (colonnes, fonction,
constante, calcul).
HAVING condition : pour inclure ou exclure des lignes aux groupes.
20/03/2022 Les Bases de données H. Meziane 2
Fonctions de groupe (ou fonctions Agrégatives)
Les fonctions de groupes sont des fonctions prédéfinies qui
donnent une valeur agrégée calculée pour les lignes
sélectionnées:
• Count (*) donne le nombre de ligne trouvées,
• Count(nom-colonne)donne le nombre de valeur de la colonne,
• AVG (nom-colonne) donne la moyenne des valeurs de la colonne,
• SUM (nom-colonne) donne la somme des valeurs de la colonne,
• MIN (nom-colonne) donne le minimum des valeurs de la colonne,
• MAX (nom-colonne) donne le maximum des valeurs de la colonne.
Ces fonction peuvent être utilisées dans la clause SELECT ou
utilisables pour effectuer des calculs d’agrégats (avec GROUP BY).
20/03/2022 Les Bases de données H. Meziane 3
Fonctions de groupe (1/2)
Pilote brevet nom nbHVol Prime Embauche typeAvion compa
PL-1 Amine 450,00 500 1965‐02‐05 A320 AA
PL-2 Mohamed 0 1995‐05‐13 A320 AA
PL-3 Laroche 1000,00 2001‐09‐11 A320 SING
PL-4 Albarik 2450,00 500 2001‐09‐21 A330 SING
PL-5 Labat 400,00 600 1965‐01‐16 A340 AA
PL-6 Francois 0 2000‐12‐24 A340 CAST
Requête : le nombre de pilotes, d’heures de vol et de primes recensées dans la
table pilote.
SELECT COUNT(*) , COUNT(nbHVol), COUNT(prime)
FROM Pilote;
COUNT(*) COUNT(nbHvol) COUNT(prime) COUNT(DISTINCT prime)
6 5 4 3
20/03/2022 Les Bases de données H. Meziane 4
Fonctions de groupe (2/2)
Pilote brevet nom nbHVol Prime Embauche typeAvion compa
PL-1 Amine 450,00 500 1965‐02‐05 A320 AA
PL-2 Mohamed 0 1995‐05‐13 A320 AA
PL-3 Laroche 1000,00 2001‐09‐11 A320 SING
PL-4 Albarik 2450,00 500 2001‐09‐21 A330 SING
PL-5 Labat 400,00 600 1965‐01‐16 A340 AA
PL-6 Francois 0 2000‐12‐24 A340 CAST
Requête : la somme des heures de vol et la moyenne des primes des pilotes de
la compagnie 'AA'.
SELECT SUM(nbHVol), AVG(prime) AS MoyennePrime
FROM Pilote WHERE Compa=‘AA’;
SUM(nbHVol) MoyennePrime
850,00 550,00
20/03/2022 Les Bases de données H. Meziane 5
Regroupements de lignes (agrégats) : GROUP BY et HAVING
Le groupement de lignes dans une requête se programme Avec
l’instruction SQL suivante :
SELECT Col1,[ Col2…], fonction1Groupe(…)[, fonction2Groupe(…)…]
FROM nomTable, [ WHERE condition ]
GROUP BY Col1 [,{Col2... }]
[ HAVING condition ]
[ORDER BY Col1 [ASC | DESC] [,{Col1 ... }] ];
• La clause WHERE de la requête permet d’exclure des lignes pour
chaque groupement, ou de rejeter des groupements entiers. Elle
s’applique donc à la totalité de la table.
• La clause GROUP BY liste les colonnes du groupement.
• La clause HAVING permet de poser des conditions sur chaque
groupement.
• La clause ORDER BY permet de trier le résultat final.
20/03/2022 Les Bases de données H. Meziane 6
Agrégat : définition
Un agrégat est un partitionnement horizontale d’une
table en sous tables en fonction des valeurs d’un ou de
plusieurs attributs de partitionnement (ou groupement),
suivi de l’application d’une fonction de calcul à chaque
attribut des sous tables obtenues.
20/03/2022 Les Bases de données H. Meziane 7
GROUP BY : Exemple (1/3)
En groupant sur la colonne compa, trois ensembles de lignes
(groupements) sont composés. Il est alors possible d’appliquer
des fonctions de groupe à chacun de ces ensembles (dont le
nombre n’est pas précisé dans la requête ni limité par le système
qui parcourt toute la table).
Pilote brevet nom nbHVol Prime Embauche typeAvion compa
PL-1 Amine 450,00 500 1965‐02‐05 A320 AA
1 PL-2 Mohamed 0 1995‐05‐13 A320 AA
PL-5 Labat 400,00 600 1965‐01‐16 A340 AA
2 PL-6 Francois 0 2000‐12‐24 A340 CAST
PL-3 Laroche 1000,00 2001‐09‐11 A320 SING
3
PL-4 Albarik 2450,00 500 2001‐09‐21 A330 SING
Il est aussi possible de grouper sur plusieurs colonnes (par exemple ici sur les
colonnes compa et typeAvion pour classifier les pilotes selon ces deux
critères).
20/03/2022 Les Bases de données H. Meziane 8
GROUP BY : Exemple (2/3)
Pilote brevet nom nbHVol Prime Embauche typeAvion compa
PL-1 Amine 450,00 500 1965‐02‐05 A320 AA
PL-2 Mohamed 0 1995‐05‐13 A320 AA
PL-3 Laroche 1000,00 2001‐09‐11 A320 SING
PL-4 Albarik 2450,00 500 2001‐09‐21 A330 SING
PL-5 Labat 400,00 600 1965‐01‐16 A340 AA
PL-6 Francois 0 2000‐12‐24 A340 CAST
Requête : Moyenne des heures de vol et des primes pour chaque compagnie
SELECT Compa, AVG(nbHVol), AVG(prime) FROM Pilote
Group By Compa;
Compa AVG(nbHVol) AVG(prime)
AA 283.33 550,00
CAST Null 0
SING 1725.00 250,00
20/03/2022 Les Bases de données H. Meziane 9
GROUP BY : Exemple (3/3)
Pilote brevet nom nbHVol Prime Embauche typeAvion compa
PL-1 Amine 450,00 500 1965‐02‐05 A320 AA
PL-2 Mohamed 0 1995‐05‐13 A320 AA
PL-3 Laroche 1000,00 2001‐09‐11 A320 SING
PL-4 Albarik 2450,00 500 2001‐09‐21 A330 SING
PL-5 Labat 400,00 600 1965‐01‐16 A340 AA
PL-6 Francois 0 2000‐12‐24 A340 CAST
Requête : la sommes des heures de vol des pilotes volant sur 'A320‘ de chaque
compagnie.
SELECT Compa, SUM(nbHVol) FROM Pilote Where typeAvion = 'A320'
Group By Compa;
Compa SUM(nbHVol)
AA 450.33
SING 1000,00
20/03/2022 Les Bases de données H. Meziane 10
GROUP BY et HAVING : Exemple
Pilote brevet nom nbHVol Prime Embauche typeAvion compa
PL-1 Amine 450,00 500 1965‐02‐05 A320 AA
PL-2 Mohamed 0 1995‐05‐13 A320 AA
PL-3 Laroche 1000,00 2001‐09‐11 A320 SING
PL-4 Albarik 2450,00 500 2001‐09‐21 A330 SING
PL-5 Labat 400,00 600 1965‐01‐16 A340 AA
PL-6 Francois 0 2000‐12‐24 A340 CAST
Requête : les compagnies (et le nombre de leurs pilotes) ayant plus d’un pilote.
SELECT Compa, COUNT(brevet) FROM Pilote
Group By Compa
HAVING COUNT(brevet)>=2;
Compa COUNT(brevet)
AA 3
SING 2
20/03/2022 Les Bases de données H. Meziane 11
Fonctions de groupe : Autre exemple
Pilote brevet nom nbHVol Prime Embauche typeAvion compa
PL-1 Amine 450,00 500 1965‐02‐05 A320 AA
PL-2 Mohamed 0 1995‐05‐13 A320 AA
PL-3 Laroche 1000,00 2001‐09‐11 A320 SING
PL-4 Albarik 2450,00 500 2001‐09‐21 A330 SING
PL-5 Labat 400,00 600 1965‐01‐16 A340 AA
PL-6 Francois 0 2000‐12‐24 A340 CAST
Requête : nombre d’heures de vol le plus élevé et la date d’embauche la plus
ancienne.
SELECT MAX(nbHVol), MIN(Embauche) AS "Date ‐"
FROM Pilote ;
Max(nbHVol) Date ‐
2450,00 1965-02-05
20/03/2022 Les Bases de données H. Meziane 12