⌂ Bases de données
Fiche 11 sur 12

Regrouper et compter

Obtenir des totaux, des moyennes et des statistiques.

Après cette fiche, vous saurez :

1Les fonctions d'agrégation

Jusqu'ici, une requête renvoyait une ligne par enregistrement. Les fonctions d'agrégation font l'inverse : elles résument plusieurs lignes en une seule valeur.

FonctionCalculeExemple
COUNT(*)Le nombre de lignescombien de livres ?
SUM(colonne)La sommetotal des montants
AVG(colonne)La moyenneannée moyenne de parution
MIN(colonne)La plus petite valeurle livre le plus ancien
MAX(colonne)La plus grande valeurle plus récent
SELECT COUNT(*) AS nombre_de_livres,
       MIN(annee) AS plus_ancien,
       MAX(annee) AS plus_recent,
       ROUND(AVG(annee)) AS annee_moyenne
FROM livres;
Résultat
nombre_de_livres | plus_ancien | plus_recent | annee_moyenne
-----------------+-------------+-------------+--------------
               5 |        1877 |        1999 |          1927

Cinq lignes en entrée, une seule en sortie.

Attention

COUNT(*) et COUNT(colonne) ne donnent pas la même chose.

SELECT COUNT(*) FROM emprunts;              -- 4 : toutes les lignes
SELECT COUNT(date_retour) FROM emprunts;    -- 1 : seulement les non-vides

COUNT(colonne) ignore les NULL. C'est parfois exactement ce qu'on veut — ici, cela compte les emprunts rendus — mais c'est une source de surprise.

En cas de doute, utilisez COUNT(*) : il compte les lignes, point.

2GROUP BY : compter par catégorie

Question : combien de livres par auteur ?

On ne veut plus un seul nombre, mais un nombre par auteur. C'est le rôle de GROUP BY.

SELECT a.nom,
       COUNT(l.num_livre) AS nombre_de_livres
FROM auteurs a
LEFT JOIN livres l ON a.num_auteur = l.num_auteur
GROUP BY a.nom
ORDER BY nombre_de_livres DESC;
Résultat
nom            | nombre_de_livres
---------------+-----------------
Zola           |                2
Simenon        |                1
Nothomb        |                1
Saint-Exupéry  |                1
Hugo           |                0

À retenir

Comment lire un GROUP BY : PostgreSQL fait des paquets.

  1. Il rassemble les lignes qui ont la même valeur de a.nom.
  2. Il applique la fonction d'agrégation à chaque paquet.
  3. Il renvoie une ligne par paquet.

Le LEFT JOIN est ce qui permet à Hugo d'apparaître avec 0. Avec un INNER JOIN, il disparaîtrait.

Et remarquez : COUNT(l.num_livre) et non COUNT(*). Avec COUNT(*), Hugo compterait 1 — sa ligne existe, même si le titre est NULL.

Attention

La règle du GROUP BY : toute colonne du SELECT qui n'est pas dans une fonction d'agrégation doit figurer dans le GROUP BY.

-- Refusé
SELECT a.nom, a.prenom, COUNT(l.num_livre)
FROM auteurs a LEFT JOIN livres l ON a.num_auteur = l.num_auteur
GROUP BY a.nom;

-- Correct
SELECT a.nom, a.prenom, COUNT(l.num_livre)
FROM auteurs a LEFT JOIN livres l ON a.num_auteur = l.num_auteur
GROUP BY a.nom, a.prenom;
ERROR: column "a.prenom" must appear in the GROUP BY clause
or be used in an aggregate function

C'est logique : dans un paquet de plusieurs lignes, quel prénom afficher ? PostgreSQL refuse de choisir à votre place.

En pratique, regroupez plutôt sur la clé primaire : GROUP BY a.num_auteur. C'est plus sûr, et ça évite de confondre deux auteurs homonymes.

3HAVING : filtrer les groupes

Question : quels auteurs ont plus d'un livre ?

SELECT a.nom, COUNT(l.num_livre) AS nb
FROM auteurs a
INNER JOIN livres l ON a.num_auteur = l.num_auteur
GROUP BY a.num_auteur, a.nom
HAVING COUNT(l.num_livre) > 1;
Résultat
nom  | nb
-----+----
Zola |  2

À retenir

WHERE et HAVING ne travaillent pas au même moment.

WHEREHAVING
Filtreles lignesles groupes
Agitavant le regroupementaprès le regroupement
Peut utiliser COUNT() ?NonOui

Exemple qui combine les deux :

SELECT a.nom, COUNT(l.num_livre) AS nb
FROM auteurs a
INNER JOIN livres l ON a.num_auteur = l.num_auteur
WHERE l.annee > 1880          -- on écarte d'abord certains livres
GROUP BY a.num_auteur, a.nom
HAVING COUNT(l.num_livre) > 1; -- puis on écarte certains auteurs

L'ordre complet des mots-clés

SELECT   colonnes et agrégats
FROM     table
JOIN     autre table ON …
WHERE    filtre sur les lignes
GROUP BY colonnes de regroupement
HAVING   filtre sur les groupes
ORDER BY tri
LIMIT    nombre;

Cet ordre est imposé et ne change jamais. Recopiez-le au dos de votre feuille.

4Les sous-requêtes

Une sous-requête est une requête placée à l'intérieur d'une autre. Elle permet de décomposer un problème en deux étapes.

Question : quels livres sont plus anciens que la moyenne ?

SELECT titre, annee
FROM livres
WHERE annee < (SELECT AVG(annee) FROM livres);
Résultat
titre        | annee
-------------+------
Germinal     |  1885
L'Assommoir  |  1877

PostgreSQL calcule d'abord la moyenne (1927), puis l'utilise comme si vous l'aviez écrite à la main.

Avec IN et NOT IN

-- Les lecteurs qui ont au moins un emprunt en cours
SELECT nom, prenom
FROM lecteurs
WHERE num_lecteur IN (
    SELECT num_lecteur FROM emprunts WHERE date_retour IS NULL
);

-- Les livres jamais empruntés
SELECT titre
FROM livres
WHERE num_livre NOT IN (
    SELECT num_livre FROM emprunts
);

À retenir

Vous pouvez souvent choisir entre une jointure et une sous-requête. Les deux donnent le même résultat.

La sous-requête a un avantage pédagogique : elle vous laisse résoudre le problème en deux temps, ce qui est plus facile à raisonner. Écrivez d'abord la sous-requête seule, vérifiez son résultat, puis emboîtez-la.

C'est la méthode à utiliser quand une requête vous résiste.

Attention

Attention à NOT IN avec des valeurs NULL.

Si la sous-requête peut renvoyer NULL, NOT IN ne renvoie aucune ligne, sans erreur ni avertissement.

C'est un piège classique. Dans le doute, préférez le motif LEFT JOIN … WHERE … IS NULL de la fiche 10, qui n'a pas ce problème.

→Exercices

1CompterRecopier

Exécutez ces requêtes et notez les résultats.

-- 1
SELECT COUNT(*) FROM livres;

-- 2
SELECT COUNT(*) FROM emprunts WHERE date_retour IS NULL;

-- 3
SELECT nationalite, COUNT(*) AS nb
FROM auteurs
GROUP BY nationalite;

-- 4
SELECT MIN(annee), MAX(annee), ROUND(AVG(annee)) FROM livres;

Solution

1. 5 livres.

2. 3 emprunts en cours.

3. Deux lignes : Française 3 (Zola, Saint-Exupéry, Hugo), Belge 2 (Simenon, Nothomb) — si vous avez ajouté Hugo à la fiche 10.

4. 1877, 1999, 1927.

Une observation importante sur la 3 : le résultat n'a que deux lignes, alors que la table en a cinq. GROUP BY a fait deux paquets et les a résumés.

C'est le changement de nature à comprendre : avec GROUP BY, une ligne du résultat ne correspond plus à une ligne de la table, mais à un groupe de lignes.

2RegrouperModifier

Écrivez la requête qui répond à chaque question.

  1. Combien de livres chaque auteur a-t-il écrits ? Les auteurs sans livre doivent apparaître avec 0.
  2. Combien d'emprunts chaque lecteur a-t-il faits ?
  3. Quels lecteurs ont emprunté plus d'un livre ?
  4. Combien de livres par siècle de parution ?
  5. Quel est le livre le plus emprunté ?

Solution

-- 1
SELECT a.nom, a.prenom, COUNT(l.num_livre) AS nb_livres
FROM auteurs a
LEFT JOIN livres l ON a.num_auteur = l.num_auteur
GROUP BY a.num_auteur, a.nom, a.prenom
ORDER BY nb_livres DESC;

-- 2
SELECT lec.nom, lec.prenom, COUNT(e.num_emprunt) AS nb_emprunts
FROM lecteurs lec
LEFT JOIN emprunts e ON lec.num_lecteur = e.num_lecteur
GROUP BY lec.num_lecteur, lec.nom, lec.prenom
ORDER BY nb_emprunts DESC;

-- 3
SELECT lec.nom, COUNT(e.num_emprunt) AS nb
FROM lecteurs lec
INNER JOIN emprunts e ON lec.num_lecteur = e.num_lecteur
GROUP BY lec.num_lecteur, lec.nom
HAVING COUNT(e.num_emprunt) > 1;

-- 4
SELECT (annee / 100) + 1 AS siecle, COUNT(*) AS nb
FROM livres
GROUP BY siecle
ORDER BY siecle;

-- 5
SELECT liv.titre, COUNT(e.num_emprunt) AS nb
FROM livres liv
INNER JOIN emprunts e ON liv.num_livre = e.num_livre
GROUP BY liv.num_livre, liv.titre
ORDER BY nb DESC
LIMIT 1;

Sur la 1 : LEFT JOIN + COUNT(l.num_livre). Les deux vont ensemble. Avec COUNT(*), Hugo afficherait 1 au lieu de 0.

Sur la 3 : ici INNER JOIN suffit, puisqu'on ne veut de toute façon que ceux qui ont emprunté. Et c'est bien HAVING et non WHERE, parce que le filtre porte sur un comptage.

Sur la 4 : annee / 100 entre deux entiers donne une division entière — 1885 / 100 = 18. On ajoute 1 pour obtenir le 19e siècle. C'est le même piège de la division entière qu'en programmation.

Sur la 5 : ORDER BY … DESC LIMIT 1. Attention, en cas d'égalité, cette requête n'en montre qu'un seul arbitrairement. C'est une limite connue.

3Les statistiques de votre baseCréer

Sur votre propre base.

  1. Une requête avec COUNT et GROUP BY.
  2. Une requête avec SUM ou AVG.
  3. Une requête avec HAVING.
  4. Une requête avec une sous-requête.
  5. Pour chacune, la question en français en commentaire.

Solution

-- Combien de consultations par médecin, et pour quel chiffre d'affaires ?
SELECT m.nom,
       m.specialite,
       COUNT(c.num_consultation) AS nb_consultations,
       SUM(c.montant) AS total_encaisse,
       ROUND(AVG(c.montant), 2) AS montant_moyen
FROM medecins m
LEFT JOIN consultations c ON m.num_medecin = c.num_medecin
GROUP BY m.num_medecin, m.nom, m.specialite
ORDER BY total_encaisse DESC NULLS LAST;

-- Quels patients sont venus plus d'une fois ?
SELECT p.nom, p.prenom, COUNT(c.num_consultation) AS nb
FROM patients p
INNER JOIN consultations c ON p.num_patient = c.num_patient
GROUP BY p.num_patient, p.nom, p.prenom
HAVING COUNT(c.num_consultation) > 1;

-- Quelles consultations coûtent plus cher que la moyenne ?
SELECT c.date_consult, p.nom, c.montant
FROM consultations c
INNER JOIN patients p ON c.num_patient = p.num_patient
WHERE c.montant > (SELECT AVG(montant) FROM consultations)
ORDER BY c.montant DESC;

Deux détails utiles introduits ici :

  • ROUND(AVG(montant), 2) arrondit la moyenne à deux décimales. Sans ça, vous obtiendriez quelque chose comme 32.0000000000000000.
  • NULLS LAST place les valeurs vides en fin de tri. Un médecin sans consultation aurait NULL comme total ; sans cette précision, PostgreSQL le mettrait en premier en tri décroissant.

Les points à vérifier dans votre travail :

  • Toute colonne hors agrégat est-elle dans le GROUP BY ?
  • Avez-vous regroupé sur la clé primaire et non sur le nom ?
  • Votre filtre porte-t-il sur des lignes (WHERE) ou sur des comptages (HAVING) ?

Je sais faire

Une case qui coince ? Relisez la partie concernée avant de passer à la fiche suivante.

← Fiche 10 Fiche 12 : Vues, états de sortie et sauvegarde →