L'agrégation conditionnelle en SQL avec CASE

Leçon 4.4 · Temps de lecture : ~8 min · Exercices pratiques ↓

L'agrégation conditionnelle calcule plusieurs métriques en une seule requête SQL en plaçant une expression CASE dans une fonction d'agrégation : SUM(CASE WHEN ... THEN ... END). Dans cette leçon, vous apprendrez les sommes, comptages et moyennes conditionnels, les erreurs d'ELSE qui faussent les chiffres, et comment transformer des lignes en colonnes (pivot).

Avec GROUP BY, vous obtenez une valeur par groupe : le nombre de paiements, le montant total. Les rapports demandent généralement plus : pour chaque employé, combien de paiements étaient petits, moyens et gros, et combien chaque tranche a rapporté. Sans agrégation conditionnelle, il faudrait exécuter trois requêtes avec des filtres WHERE différents et combiner les résultats à la main.

L'idée principale

Une fonction d'agrégation traite chaque ligne du groupe. Un CASE à l'intérieur décide de la valeur apportée par chaque ligne :

SUM(CASE WHEN condition THEN value ELSE 0 END)
COUNT(CASE WHEN condition THEN 1 END)
AVG(CASE WHEN condition THEN value END)

Les lignes qui ne remplissent pas la condition apportent 0 à une somme ou NULL, que les fonctions d'agrégation ignorent. Chaque métrique n'est donc calculée que sur « ses » lignes, tandis que toutes les métriques sont calculées en un seul passage sur la table.

Somme conditionnelle

SELECT staff_id,
       SUM(CASE WHEN amount < 2 THEN amount ELSE 0 END) AS low_total,
       SUM(CASE WHEN amount >= 2 AND amount < 6 THEN amount ELSE 0 END) AS medium_total,
       SUM(CASE WHEN amount >= 6 THEN amount ELSE 0 END) AS high_total,
       SUM(amount) AS total
FROM payment
GROUP BY staff_id;

Résultat : pour chaque employé, le chiffre d'affaires des petits, moyens et gros paiements, et le total. Les trois tranches additionnées donnent le total.

Pour SUM, ELSE 0 est correct : les lignes hors tranche n'ajoutent rien. Sans ELSE, une tranche sans aucune ligne correspondante renvoie NULL au lieu de 0.

Comptage conditionnel

SELECT staff_id,
       COUNT(CASE WHEN amount < 2 THEN 1 END) AS low_payments,
       COUNT(CASE WHEN amount >= 2 AND amount < 6 THEN 1 END) AS medium_payments,
       COUNT(CASE WHEN amount >= 6 THEN 1 END) AS high_payments
FROM payment
GROUP BY staff_id;

Résultat : le nombre de paiements de chaque tranche pour chaque employé.

COUNT(expression) compte les valeurs non NULL. Quand la condition est fausse et qu'il n'y a pas d'ELSE, CASE renvoie NULL, et la ligne n'est pas comptée.

Une erreur fréquente : COUNT(CASE WHEN ... THEN 1 ELSE 0 END). Zéro n'est pas NULL, donc COUNT compte toutes les lignes et les trois colonnes affichent le même nombre. Si vous préférez ELSE 0, utilisez SUM : SUM(CASE WHEN ... THEN 1 ELSE 0 END).

Une colonne peut aussi être comptée directement. COUNT(return_date) ne compte que les locations rendues :

SELECT staff_id,
       COUNT(*) AS all_rentals,
       COUNT(return_date) AS returned,
       COUNT(CASE WHEN return_date IS NULL THEN 1 END) AS not_returned
FROM rental
GROUP BY staff_id;

Résultat : pour chaque employé, toutes les locations, les locations rendues et celles pas encore rendues.

Moyenne conditionnelle

SELECT staff_id,
       AVG(amount) AS avg_payment,
       AVG(CASE WHEN amount >= 5 THEN amount END) AS avg_big_payment
FROM payment
GROUP BY staff_id;

Résultat : pour chaque employé, la moyenne de tous les paiements et la moyenne des paiements de 5 $ et plus.

Pour AVG, n'écrivez pas ELSE 0. La moyenne est la somme divisée par le nombre de valeurs ; les zéros seraient comptés comme des valeurs et feraient baisser la moyenne. Sans ELSE, les lignes non correspondantes donnent NULL et sont ignorées.

Pivot : transformer des lignes en colonnes

Un pivot affiche les valeurs d'une catégorie dans des colonnes séparées. Un GROUP BY rental_rate, rating classique renvoie une ligne par combinaison. Avec l'agrégation conditionnelle, chaque classification devient une colonne :

SELECT rental_rate,
       COUNT(CASE WHEN rating = 'G' THEN 1 END) AS g,
       COUNT(CASE WHEN rating = 'PG' THEN 1 END) AS pg,
       COUNT(CASE WHEN rating = 'PG-13' THEN 1 END) AS pg13,
       COUNT(CASE WHEN rating = 'R' THEN 1 END) AS r,
       COUNT(CASE WHEN rating = 'NC-17' THEN 1 END) AS nc17,
       COUNT(*) AS all_films
FROM film
GROUP BY rental_rate
ORDER BY rental_rate;

Résultat : trois lignes, une par tarif de location (0,99 $, 2,99 $, 4,99 $), avec le nombre de films de chaque classification dans des colonnes séparées.

Cela fonctionne aussi avec d'autres agrégats. La durée moyenne des films par classification, toujours sans ELSE :

SELECT rental_rate,
       ROUND(AVG(CASE WHEN rating = 'G' THEN length END), 1) AS g_avg_length,
       ROUND(AVG(CASE WHEN rating = 'R' THEN length END), 1) AS r_avg_length
FROM film
GROUP BY rental_rate
ORDER BY rental_rate;

Un pivot par jour de la semaine, construit à partir d'une date :

SELECT MONTH(rental_date) AS rental_month,
       SUM(CASE WHEN DAYOFWEEK(rental_date) IN (1, 7) THEN 1 ELSE 0 END) AS weekend_rentals,
       SUM(CASE WHEN DAYOFWEEK(rental_date) BETWEEN 2 AND 6 THEN 1 ELSE 0 END) AS weekday_rentals
FROM rental
GROUP BY MONTH(rental_date)
ORDER BY rental_month;

Résultat : pour chaque mois, le nombre de locations le week-end et en semaine. Dans MySQL, DAYOFWEEK() renvoie 1 pour dimanche et 7 pour samedi.

Certains SGBD ont un opérateur PIVOT (SQL Server, Oracle), mais l'agrégation conditionnelle fonctionne partout et constitue le choix portable.

Parts et ratios

Deux agrégats conditionnels peuvent être combinés dans une même expression :

SELECT customer_id,
       COUNT(*) AS payments,
       ROUND(100 * SUM(CASE WHEN amount >= 5 THEN 1 ELSE 0 END) / COUNT(*), 1) AS big_payment_pct
FROM payment
GROUP BY customer_id
ORDER BY big_payment_pct DESC
LIMIT 10;

Résultat : les 10 clients avec la plus grande part de paiements de 5 $ et plus.

La clause FILTER

PostgreSQL et SQLite (3.30+) prennent en charge une syntaxe standard plus courte, FILTER :

-- PostgreSQL, SQLite
SELECT customer_id,
       COUNT(*) AS all_payments,
       COUNT(*) FILTER (WHERE amount >= 5) AS big_payments,
       SUM(amount) FILTER (WHERE amount >= 5) AS big_total
FROM payment
GROUP BY customer_id;

Cela équivaut à COUNT(CASE WHEN amount >= 5 THEN 1 END). MySQL, MariaDB et SQL Server ne prennent pas en charge FILTER, donc CASE reste la forme portable.

Points clés :

• L'agrégation conditionnelle est une fonction d'agrégation avec un CASE à l'intérieur : plusieurs métriques en une requête et un seul passage.
• SUM(CASE ... ELSE 0 END) pour les sommes ; COUNT(CASE ... END) sans ELSE pour les comptages.
• COUNT(CASE ... ELSE 0 END) compte toutes les lignes : une erreur fréquente.
• AVG(CASE ...) sans ELSE, sinon les zéros font baisser la moyenne.
• Un pivot avec CASE transforme les valeurs d'une catégorie en colonnes et fonctionne dans tous les SGBD.

Questions fréquentes

Qu'est-ce que l'agrégation conditionnelle en SQL ?

C'est une fonction d'agrégation dont l'argument est une expression CASE, par exemple SUM(CASE WHEN amount >= 5 THEN amount ELSE 0 END). Chaque métrique n'est calculée que sur les lignes qui remplissent sa condition, donc une seule requête peut renvoyer de nombreuses métriques filtrées différemment.

Pourquoi COUNT(CASE WHEN ... ELSE 0 END) compte-t-il toutes les lignes ?

COUNT compte les valeurs qui ne sont pas NULL, et 0 n'est pas NULL. Supprimez l'ELSE pour que les lignes non correspondantes renvoient NULL, ou utilisez SUM(CASE WHEN ... THEN 1 ELSE 0 END).

Comment transformer des lignes en colonnes dans MySQL ?

MySQL n'a pas d'opérateur PIVOT. Utilisez un agrégat conditionnel par colonne de sortie, COUNT(CASE WHEN rating = 'G' THEN 1 END) AS g, et groupez par la dimension des lignes.