Probleme – Deux agrégats dans une requête, et pourquoi ils sont faux

1. Séparément, les deux requêtes sont sans mystère.

SELECT e.promo, COUNT(*) AS nb_notes
FROM Eleve e JOIN Note n ON n.eleve = e.id GROUP BY e.promo;
-- 2026 : 4   |   2027 : 1

SELECT promo, COUNT(*) AS nb_enseignements FROM Enseigne GROUP BY promo;
-- 2026 : 4   |   2027 : 2

2. Fusionnées, elles mentent.

SELECT e.promo, COUNT(*) AS lignes
FROM Eleve e
JOIN Note n ON n.eleve = e.id
JOIN Enseigne g ON g.promo = e.promo
GROUP BY e.promo;
-- 2026 : 16      2027 : 2

Seize lignes pour $2026$, là où l'on en attendait quatre. Et si l'on avait écrit COUNT(n.valeur) d'un côté et COUNT(g.prof) de l'autre, les deux auraient rendu $16$.

3. Le mécanisme, et il est arithmétique. Les deux jointures sont indépendantes : rien ne relie une note à un enseignement. Le moteur produit donc, pour chaque note de la promotion, toutes les lignes d'enseignement de cette promotion — soit $4\times 4=16$ pour $2026$, et $1\times 2=2$ pour $2027$. C'est un produit cartésien à l'intérieur de chaque paquet, et le GROUP BY n'y peut rien : il compte ce qu'on lui donne.

Sur SUM, le dégât est plus visible encore :

$204=51\times 4$ et $30=15\times 2$ : chaque note a été comptée autant de fois qu'il y a d'enseignements. Le facteur n'est pas constant — il vaut $4$ pour une promotion et $2$ pour l'autre — de sorte qu'aucune division ne rattrapera l'erreur en bloc.

4. Les deux réparations.

(a) Agréger avant de joindre. On calcule chaque total dans sa propre requête, puis on rapproche les résultats.

SELECT a.promo, a.nb_notes, b.nb_enseignements
FROM (SELECT e.promo, COUNT(*) AS nb_notes FROM Eleve e
      JOIN Note n ON n.eleve = e.id GROUP BY e.promo) a
JOIN (SELECT promo, COUNT(*) AS nb_enseignements FROM Enseigne
      GROUP BY promo) b ON b.promo = a.promo;
-- 2026 | 4 | 4        2027 | 1 | 2

Chaque sous-requête ne voit qu'une seule table de détail : aucune multiplication n'est possible. C'est la réparation générale, et la seule qui fonctionne pour SUM et AVG.

(b) Compter des entités distinctes. Quand la grandeur voulue est un nombre d'objets et non un nombre de lignes, COUNT(DISTINCT ...) traverse la duplication :

SELECT e.promo, COUNT(DISTINCT n.eleve) AS eleves_notes,
       COUNT(DISTINCT g.prof) AS professeurs
FROM Eleve e JOIN Note n ON n.eleve = e.id
             JOIN Enseigne g ON g.promo = e.promo
GROUP BY e.promo;
-- 2026 | 2 | 3        2027 | 1 | 2

Vérifié : les mêmes chiffres sortent des deux requêtes séparées. Mais cette réparation ne marche que pour COUNT — il n'existe pas de « SUM DISTINCT » qui aurait un sens, puisque deux notes égales doivent bien être additionnées deux fois.