Fonctions d’agrégation SQL et GROUP BY

Une fonction d’agrégation combine les valeurs de plusieurs lignes en un seul résultat. Oracle l’appelle aussi fonction de groupe. Sans GROUP BY, une requête d’agrégation traite toutes les lignes qui satisfont WHERE comme un seul groupe et renvoie une ligne de synthèse.

Partir de données vérifiables

Chaque ligne de orders représente une commande. Les colonnes de nombres ci-dessous contiennent des entiers ; order_id est la clé primaire. Les dates sont au format texte ISO dans SQLite et de type DATE dans PostgreSQL.

Choisir la mesure

SUM(amount) additionne les montants. AVG(amount) donne leur moyenne arithmétique. MIN(amount) trouve le plus petit montant et MAX(amount) le plus grand. Tu peux demander les quatre dans une seule requête :

SELECT SUM(amount) AS total, AVG(amount) AS avg_amount, MIN(amount) AS smallest, MAX(amount) AS largest FROM orders;

Le résultat est une ligne : total 780, moyenne 97,5, minimum 30 et maximum 200. Additionne les huit montants pour obtenir 780, puis divise par huit pour obtenir 97,5. Chaque fonction utilise les mêmes huit lignes en entrée. Des alias clairs après AS rendent les en-têtes du résultat utiles ; un en-tête généré comme sum indique peu de chose sur ce qui a été additionné.

Filtrer avant de calculer

Pour synthétiser les commandes de East, écris SELECT COUNT(*) AS orders, SUM(amount) AS total FROM orders WHERE region = 'East';. Le filtre conserve les montants 120, 45 et 95. La requête renvoie trois commandes et un total de 260. WHERE choisit les lignes en entrée avant l’exécution de tout agrégat.

Un agrégat peut aussi prendre une expression calculée. Dans une table contenant quantity et unit_price, SUM(quantity * unit_price) calcule d’abord le chiffre d’affaires de chaque ligne, puis additionne ces chiffres d’affaires. Deux lignes, l’une de deux articles à 5 et l’autre de trois articles à 10, contribuent pour 10 et 30, soit un total de 40. SUM(quantity) * SUM(unit_price) multiplie quant à lui 5 par 15 et donne 75. Placer le calcul dans SUM conserve le prix correspondant à chaque quantité.

Interpréter correctement les valeurs extrêmes

MIN(order_date) vaut 2026-01-05 et MAX(order_date) vaut 2026-03-21. Les valeurs de date et les dates au format texte ISO se trient chronologiquement. Un texte tel que '03/21/2026' suit l’ordre des caractères, qui ne correspond pas toujours à l’ordre des dates. Les valeurs textuelles extrêmes suivent aussi la collation de la base de données, c’est-à-dire ses règles de comparaison du texte. Ici, les clients aux extrémités sont 'Ana' et 'Eli' ; un mélange de majuscules et de minuscules peut modifier l’ordre du texte.

MAX(amount) renvoie un montant, et non la commande entière qui le contient. Pour afficher un client ayant passé une commande du montant le plus élevé, utilise SELECT customer, amount FROM orders ORDER BY amount DESC LIMIT 1;. PostgreSQL rejette une colonne simple customer à côté de MAX(amount) sans regroupement approprié. SQLite possède une règle particulière pour un seul MIN ou MAX qui peut renvoyer une colonne voisine de la ligne contenant la valeur extrême, mais les valeurs extrêmes ex aequo nécessitent toujours une règle de départage.

« Combien ? » peut porter sur les lignes, les valeurs enregistrées ou les valeurs différentes. Ce sont des comptages différents, même lorsqu’ils donnent par hasard le même nombre dans une petite table.

Trois formes de COUNT

Dans la table des commandes, les régions sont East, West, East, North, West, East, North et NULL. SELECT COUNT(*) AS order_rows, COUNT(region) AS with_region, COUNT(DISTINCT region) AS regions FROM orders; renvoie 8, 7 et 3. Les noms de région répétés sont comptés séparément par COUNT(region), mais chaque nom ne contribue qu’une fois au comptage des valeurs distinctes.

Pour répondre à « Combien de clients différents ont commandé ? », utilise COUNT(DISTINCT customer). Ana, Ben et Chen apparaissent chacun deux fois, tandis que Dara et Eli apparaissent une fois : la réponse est donc cinq clients. COUNT(customer) renvoie huit, car chaque commande indique un client. Ce comptage mesure les lignes de commande qui nomment un client, et non les personnes.

Un zéro enregistré compte aussi

Les remises sont 10, NULL, 5, NULL, 0, NULL, 15 et NULL. COUNT(discount) renvoie quatre. Le zéro indique une valeur connue et compte comme 10, 5 et 15. Une chaîne vide est aussi une valeur dans SQLite et PostgreSQL ; Oracle traite une chaîne vide comme NULL.

Soustrais le nombre de valeurs non NULL du nombre de lignes pour mesurer les données manquantes : SELECT COUNT(*) - COUNT(region) AS missing_region FROM orders; renvoie un. Cela fonctionne parce que les deux comptages utilisent exactement les mêmes lignes en entrée.

Listes distinctes et comptages distincts

SELECT DISTINCT region FROM orders; liste East, West, North et une ligne NULL. Son rôle est de renvoyer les différentes valeurs, et NULL reste dans cette liste. COUNT(DISTINCT region) renvoie trois, car le comptage ignore NULL. Ne déduis pas un nombre de valeurs distinctes en comptant une liste sans avoir décidé si sa ligne NULL doit être incluse.

DISTINCT peut aussi figurer dans SUM ou AVG. Les quantités sont 2, 1, 3, 4, 1, 2, 5 et 1. Leur somme ordinaire est de 19. SUM(DISTINCT quantity) ne conserve que 1, 2, 3, 4 et 5, ce qui donne 15. Les quantités répétées correspondent généralement à de véritables ventes supplémentaires. Les supprimer change la mesure ; cela ne corrige pas des enregistrements en double.

Compter les combinaisons en deux étapes

Pour obtenir les paires région-client différentes, crée d’abord une liste distincte :

SELECT COUNT(*) FROM (SELECT DISTINCT region, customer FROM orders) AS pairs;

La requête interne renvoie cinq paires : East/Ana, East/Dara, North/Chen, West/Ben et NULL/Eli. Le COUNT(*) externe compte ces cinq lignes, y compris NULL/Eli. Cette forme fonctionne dans SQLite et PostgreSQL. MySQL accepte aussi COUNT(DISTINCT region, customer), mais cette forme exclut les combinaisons contenant NULL et peut donc répondre à une autre question. Dans SQLite et PostgreSQL, la forme avec des éléments séparés par une virgule est invalide.

Enfin, un comptage ne renvoie jamais NULL. SELECT COUNT(*) FROM orders WHERE region = 'South'; renvoie une ligne de synthèse contenant zéro, car aucune commande ne correspond.

NULL signifie qu’une valeur est manquante ou inconnue. Il modifie les valeurs reçues par un agrégat : un total ou une moyenne peut donc être valide en SQL tout en mesurant autre chose que ce que tu voulais.

Déterminer le dénominateur

Supposons que scores contienne Ana 80, Ben NULL, Chen 70 et Dara 90. SUM(score) vaut 240, COUNT(score) vaut trois et COUNT(*) vaut quatre. AVG(score) vaut 80 : les trois scores enregistrés totalisent 240 et sont divisés par trois.

Les cinq agrégats standard ignorent les arguments NULL. COUNT(*) compte toutes les lignes parce qu’il n’évalue pas d’argument de colonne. MIN(score) vaut 70 et MAX(score) vaut 90 ; NULL ne devient ni le plus petit ni le plus grand score. C’est différent du tri, où les valeurs NULL occupent une extrémité du résultat : en premier dans un tri croissant sous SQLite, en dernier dans un tri croissant sous PostgreSQL.

Une moyenne calculée à la main nécessite le nombre de valeurs non NULL : 1.0 * SUM(score) / NULLIF(COUNT(score), 0). L’opérande décimal conserve les résultats fractionnaires et NULLIF transforme un diviseur nul en NULL. Une division par COUNT(*) donnerait 60 et traiterait en pratique le score manquant comme un zéro.

Déterminer le sens d’une valeur par défaut

COALESCE renvoie son premier argument non NULL. Si Ben n’a pas été noté, laisse le score manquant et calcule la moyenne des résultats connus. Si un examen manqué est enregistré comme NULL mais doit compter pour zéro, AVG(COALESCE(score, 0)) calcule la moyenne de 80, 0, 70 et 90, soit 60. Le sens des données détermine si ce remplacement est justifié.

Compare le placement de COALESCE à l’intérieur et à l’extérieur d’une fonction. AVG(COALESCE(score, 0)) modifie les valeurs utilisées pour la moyenne. COALESCE(AVG(score), 0) modifie uniquement un résultat final NULL ; pour ces scores, il donne toujours 80. NVL(x, 0) dans Oracle est une autre forme à deux arguments pour remplacer NULL.

Distinguer l’absence de lignes de l’absence de valeurs enregistrées

Avec WHERE region = 'South', aucune commande ne reste. Une requête d’agrégation sans GROUP BY renvoie tout de même une ligne : COUNT(*) vaut zéro, tandis que SUM(amount), AVG(amount), MIN(amount) et MAX(amount) valent NULL. Pour afficher un total vide sous la forme zéro, utilise COALESCE(SUM(amount), 0). SQLite propose aussi TOTAL(amount), qui renvoie 0.0 pour une entrée vide.

Conserve maintenant les quatre commandes dont la remise est NULL. Il y a des lignes, mais aucune valeur de remise enregistrée : COUNT(*) vaut quatre, COUNT(discount) vaut zéro et SUM(discount) vaut NULL. Zéro valeur enregistrée et quatre valeurs manquantes sont deux constats différents.

Vérifier les expressions avant leur entrée dans SUM

Un calcul arithmétique avec NULL produit NULL. Dans SUM(amount - discount), les quatre commandes sans remise enregistrée n’apportent aucune valeur. Seules les lignes dont la remise est connue contribuent : 110, 40, 60 et 135, soit un total de 345.

Si une remise manquante signifie qu’il n’y a pas de remise, écris SUM(amount - COALESCE(discount, 0)). Chaque commande contribue alors au résultat ; le montant total de 780 moins les remises enregistrées de 30 donne 750. Entourer la somme entière de COALESCE ne rétablirait pas les commandes omises, car 345 n’est déjà pas NULL.

Pour repérer les valeurs manquantes, utilise IS NULL ou IS NOT NULL. WHERE discount IS NULL trouve quatre commandes. discount = NULL n’est jamais vrai et ne peut donc pas les sélectionner.

GROUP BY rassemble les lignes qui partagent une valeur de regroupement. Chaque agrégat s’exécute séparément dans chaque groupe et le résultat contient une ligne pour chaque groupe existant après filtrage.

Déterminer ce que signifie une ligne de résultat

« Total des ventes par région » demande une ligne de résultat par région. Commence par écrire cette phrase, puis utilise region comme colonne de regroupement :

SELECT region, COUNT(*) AS orders, SUM(amount) AS total FROM orders GROUP BY region;

Les trois montants de East, 120, 45 et 95, forment un groupe. Son effectif est de trois et son total de 260. North compte deux commandes pour un total de 350 ; West en compte deux pour un total de 140. La commande d’Eli forme un groupe de région NULL avec une commande d’un total de 30. Il y a quatre lignes de résultat, et non huit.

Lis la requête comme une succession d’étapes : FROM fournit les commandes, un éventuel WHERE retire des lignes, GROUP BY rassemble les lignes restantes par région et les agrégats synthétisent chaque ensemble. SELECT affiche son libellé et ses mesures. Le regroupement n’élimine pas les petites commandes d’un groupe ; elles contribuent toutes au résultat, sauf si un filtre sur les lignes les a retirées.

Garder les catégories manquantes visibles

Les lignes dont la colonne de regroupement vaut NULL appartiennent à un seul groupe NULL. C’est utile : un rapport peut montrer l’importance de l’activité sans région enregistrée. Il ne traite pas chaque région manquante comme une catégorie différente.

Pour afficher un libellé lisible, sélectionne COALESCE(region, 'No region') AS region_label et conserve GROUP BY region. Le libellé modifie la valeur affichée. Pour exclure ces lignes des calculs, ajoute WHERE region IS NOT NULL avant GROUP BY. Les totaux obtenus ont une somme de 750, car le montant de 30 d’Eli a été exclu.

Une requête groupée sur une entrée sans lignes ne renvoie aucun groupe. Par exemple, filtrer la table sur South puis regrouper par région ne renvoie aucune ligne de résultat. C’est différent d’un agrégat sur toute la table sans GROUP BY, qui produit une ligne de synthèse même lorsque l’entrée est vide.

Trier explicitement les groupes

GROUP BY ne garantit aucun ordre de sortie. Pour lister d’abord les régions aux totaux les plus élevés, ajoute ORDER BY total DESC. L’alias total est autorisé dans ORDER BY ; ORDER BY SUM(amount) DESC produit le même tri.

Un rapport par client peut sélectionner les trois premiers totaux :

SELECT customer, SUM(amount) AS total FROM orders GROUP BY customer ORDER BY total DESC, customer LIMIT 3;

Il renvoie Chen 350, Ana 165 et Ben 140. La seconde clé de tri, customer, donne un ordre reproductible aux totaux ex aequo. LIMIT 3 ne renvoie que trois lignes ; il ne conserve pas tous les ex aequo à la limite. SQL Server exprime cette limite avec SELECT TOP 3, tandis qu’Oracle prend en charge FETCH FIRST 3 ROWS ONLY.

Lors d’un tri croissant des libellés de catégorie, SQLite place normalement NULL en premier et PostgreSQL en dernier. Précise l’ordre voulu lorsque cette position compte.

Vérifier la colonne de regroupement

SELECT DISTINCT region FROM orders et SELECT region FROM orders GROUP BY region renvoient les mêmes libellés de région distincts, y compris NULL. Utilise le regroupement lorsque tu as aussi besoin d’une mesure pour chaque libellé.

Un regroupement selon order_id, qui est unique, donne huit groupes d’une ligne chacun. Chaque effectif vaut un et chaque total de montant est égal au montant initial. Pour un rapport régional, ce résultat indique que la colonne de regroupement est trop détaillée.

Un rapport groupé doit faire tenir plusieurs lignes en entrée dans une seule ligne de résultat. Chaque valeur affichée doit donc avoir un sens clair pour l’ensemble du groupe. C’est pourquoi SQL limite les colonnes que tu peux sélectionner.

Respecter la règle de regroupement portable

Un élément d’un SELECT groupé doit être une colonne de regroupement, une expression construite à partir de colonnes de regroupement, un agrégat ou une constante. Considère cette requête :

SELECT region, customer, COUNT(*) FROM orders GROUP BY region;

East contient Ana, Ana et Dara. Le groupe a une seule région et un seul effectif, mais deux noms de clients différents. La requête n’indique pas quel client afficher dans l’unique résultat pour East. PostgreSQL rejette la colonne customer non regroupée.

Le même problème se pose sans GROUP BY : SELECT customer, SUM(amount) FROM orders associe un total sur toute la table à un nom individuel. Le total n’a pas un seul client pour le désigner.

Corriger la requête selon la question

Il existe trois corrections utiles, chacune ayant un sens différent :

• Pour obtenir une ligne par région, retire customer : SELECT region, COUNT(*) FROM orders GROUP BY region;.
• Pour obtenir une ligne par région et par client, regroupe selon les deux : SELECT region, customer, COUNT(*) FROM orders GROUP BY region, customer;.
• Pour obtenir le nombre de clients par région, agrège les noms : SELECT region, COUNT(DISTINCT customer) AS customers FROM orders GROUP BY region;.

La dernière requête indique deux clients dans East et un dans chacun des autres groupes de régions. Ajouter une colonne à GROUP BY peut modifier le niveau de détail du rapport : choisis donc la correction en fonction de la question, et pas seulement du message d’erreur.

Une colonne de regroupement peut être omise de SELECT. SELECT COUNT(*) FROM orders GROUP BY region; est valide et renvoie quatre effectifs : trois, deux, deux et un, sans ordre garanti. Ces nombres sont difficiles à utiliser sans leurs libellés de région.

Appliquer la règle au-delà de SELECT

Un ORDER BY customer groupé pose le même problème lorsqu’on regroupe uniquement par région : quel client doit déterminer la place de East ? Les conditions HAVING d’une requête groupée doivent aussi décrire un groupe entier. Utilise-y des colonnes de regroupement ou des expressions d’agrégation.

Reconnaître le comportement permissif d’une base de données

SQLite accepte une colonne nue, c’est-à-dire une colonne sélectionnée qui n’est ni regroupée ni agrégée. Il prend une valeur dans une ligne en entrée du groupe. Un résultat apparemment correct comme 'Ana' à côté de East ne signifie pas qu’Ana représente toutes les commandes de cette région.

Avec exactement un agrégat MIN ou MAX intégré, SQLite prend les valeurs des colonnes nues dans une ligne contenant la valeur extrême. Les ex aequo et la présence de plusieurs agrégats de valeurs extrêmes rendent incertaine la ligne choisie. Ce raccourci est propre à la base de données. Trier les lignes initiales est plus clair lorsque tu cherches une commande dont le montant est extrême.

Le mode ONLY_FULL_GROUP_BY activé par défaut dans MySQL rejette les valeurs non regroupées qui ne sont pas déterminées par le groupe. ANY_VALUE(customer) autorise explicitement une valeur arbitraire, mais ne trouve pas le client associé à un total choisi. SQL Server et Oracle rejettent aussi les colonnes ordinaires non regroupées.

PostgreSQL et MySQL reconnaissent une exception pour la clé primaire : regrouper selon la clé primaire d’une table identifie une seule ligne, ce qui détermine ses autres colonnes. SELECT order_id, customer, SUM(amount) FROM orders GROUP BY order_id fonctionne dans PostgreSQL. Énumérer chaque colonne simple affichée dans GROUP BY reste la méthode portable.

Certains rapports nécessitent une combinaison de catégories ou une catégorie calculée à partir de chaque ligne. SQL peut regrouper selon plusieurs colonnes ou selon une expression. Le choix essentiel reste ce que représente une ligne de résultat.

Regrouper selon une combinaison

GROUP BY region, customer ne rassemble les lignes que lorsque les deux valeurs correspondent. Sur la table des commandes, exécute :

SELECT region, customer, COUNT(*) AS orders, SUM(amount) AS total FROM orders GROUP BY region, customer ORDER BY region, customer;

Les résultats sont East/Ana : deux commandes, 165 ; East/Dara : une, 95 ; North/Chen : deux, 350 ; West/Ben : deux, 140 ; et NULL/Eli : une, 30. La somme des deux résultats de East est égale au total régional de 260.

Un regroupement selon customer, region forme les mêmes combinaisons. L’ordre des colonnes dans GROUP BY ne trie pas le résultat. La clause ORDER BY distincte ci-dessus détermine son affichage, sous réserve de la position de NULL par défaut dans chaque base de données.

Seules les combinaisons présentes dans l’entrée apparaissent. Il n’y a pas de ligne West/Ana avec un effectif de zéro. Le regroupement synthétise les lignes existantes ; il ne crée pas toutes les paires possibles de libellés de catégories.

Ajouter des détails seulement si nécessaire

L’ajout d’une colonne ne divise un groupe que si ses lignes contiennent des valeurs différentes dans cette colonne. Ici, le regroupement par client seul donne déjà cinq groupes. L’ajout de la région en donne toujours cinq, car chaque client n’a qu’une seule valeur de région. Ajouter order_date à un rapport régional crée des groupes plus petits pour les différentes dates et change la question à laquelle le rapport répond.

Regrouper les dates par année et par mois

Dans SQLite, utilise strftime('%Y-%m', order_date) pour créer un libellé comme '2026-02' :

SELECT strftime('%Y-%m', order_date) AS month, COUNT(*) AS orders, SUM(amount) AS total FROM orders GROUP BY strftime('%Y-%m', order_date) ORDER BY month;

Janvier compte deux commandes pour un total de 200, février en compte trois pour un total de 305 et mars en compte trois pour un total de 275. Dans PostgreSQL, remplace les deux occurrences de strftime('%Y-%m', order_date) par to_char(order_date, 'YYYY-MM') pour une colonne DATE.

Un regroupement selon le seul numéro du mois combine les mêmes mois de différentes années. Janvier 2025 et janvier 2026 formeraient un seul groupe. Conserver l’année dans l’expression de regroupement permet de distinguer les mois du calendrier.

Répéter l’expression dans GROUP BY rend le regroupement explicite. SQLite, PostgreSQL et MySQL acceptent aussi l’alias de sortie, comme dans GROUP BY month ; SQL Server n’accepte pas à cet endroit un alias SELECT du même niveau.

Créer des catégories avec CASE

Une expression CASE peut attribuer un libellé à chaque commande avant le regroupement :

SELECT CASE WHEN amount >= 100 THEN 'large' ELSE 'small' END AS size, COUNT(*) AS orders, SUM(amount) AS total FROM orders GROUP BY CASE WHEN amount >= 100 THEN 'large' ELSE 'small' END;

La catégorie large contient 120, 200 et 150 : trois commandes pour un total de 470. La catégorie small contient cinq commandes pour un total de 310. Le seuil est inclus : un montant d’exactement 100 appartient donc à 'large'. La même expression CASE figure dans l’affichage et dans le regroupement afin que les deux utilisent la même définition des catégories.

HAVING teste les groupes après le calcul de leurs agrégats. Utilise-le pour une demande comme « régions dont le total des ventes dépasse 200 ». La condition décrit chaque total régional, et non une commande individuelle.

Calculer d’abord, conserver les groupes ensuite

SELECT region, COUNT(*) AS orders, SUM(amount) AS total FROM orders GROUP BY region HAVING SUM(amount) > 200;

Avant HAVING, les totaux régionaux sont East 260, North 350, West 140 et NULL 30. La condition conserve East avec trois commandes et North avec deux. Elle retire les deux autres groupes du résultat ; elle ne retire pas certaines commandes à l’intérieur de East ou de North.

Dans une requête SQL écrite, place HAVING après GROUP BY et avant ORDER BY. Le tri s’applique ensuite aux groupes conservés par la condition.

Filtrer selon des mesures non affichées

SELECT region FROM orders GROUP BY region HAVING COUNT(*) >= 2; renvoie East, North et West. La base de données peut compter les lignes d’un groupe pour décider s’il remplit la condition sans afficher ce nombre. HAVING et SELECT n’ont pas besoin de listes d’agrégats identiques.

Tu peux combiner des conditions sur les groupes. HAVING COUNT(*) >= 2 AND AVG(amount) > 80 conserve East et North. East compte trois commandes et une moyenne d’environ 86,67 ; North en compte deux et une moyenne de 175. West compte assez de commandes, mais sa moyenne n’est que de 70. Les deux tests doivent être satisfaits, car ils sont reliés par AND ; avec OR, un seul test suffirait.

Trouver les valeurs répétées

Le regroupement et le comptage permettent aussi de trouver des doublons :

SELECT customer, COUNT(*) AS orders FROM orders GROUP BY customer HAVING COUNT(*) > 1;

Ana, Ben et Chen ont chacun deux commandes et remplissent la condition. Dara et Eli en ont une et ne la remplissent pas. Pour trouver les adresses électroniques répétées dans une table de clients, le même modèle regroupe selon email et conserve les effectifs supérieurs à un. L’effectif est le nombre d’enregistrements qui partagent cette valeur.

Utiliser l’expression nécessaire à la condition

Répète l’agrégat dans HAVING : HAVING SUM(amount) > 200. PostgreSQL et le SQL standard n’autorisent pas l’alias de sortie total dans cette condition au même niveau, contrairement à SQLite et MySQL. Un alias peut être utilisé sans problème dans un ORDER BY total DESC ultérieur.

Une valeur arrondie affichée et la valeur testée peuvent différer. La moyenne exacte de East est de 260 divisé par trois. Si tu sélectionnes ROUND(AVG(amount), 2) mais écris HAVING AVG(amount) > 86.67, East est retiré : sa moyenne non arrondie est inférieure à 86,67. North reste. La condition utilise exactement l’expression écrite dans HAVING. Seul HAVING ROUND(AVG(amount), 2) ... testerait la valeur arrondie.

Filtrer le groupe global unique

HAVING peut aussi fonctionner sans GROUP BY explicite. SELECT COUNT(*) FROM orders HAVING COUNT(*) > 5; traite les huit commandes comme un seul groupe, satisfait le test et renvoie une ligne contenant huit. Remplacer la condition par COUNT(*) > 10 ne renvoie aucune ligne, et non une ligne contenant zéro.

PostgreSQL prend en charge ce modèle, tout comme SQLite depuis la version 3.39.0. Dans une installation SQLite plus ancienne, une requête utilisant HAVING nécessite un GROUP BY explicite.

Une condition doit être placée là où ce qu’elle décrit existe. Les commandes individuelles existent avant le regroupement. Un total régional n’existe qu’après le regroupement et l’addition des commandes.

Suivre l’ordre logique

Une requête SQL s’écrit dans l’ordre SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY, LIMIT. Pour raisonner sur le résultat, suis cet ordre logique :

1. FROM fournit les lignes.
2. WHERE conserve les lignes qui remplissent la condition.
3. GROUP BY forme des groupes et les agrégats calculent leurs mesures.
4. HAVING conserve les groupes qui remplissent la condition.
5. SELECT produit les valeurs de sortie ; DISTINCT supprime les lignes de résultat en double si cela est demandé.
6. ORDER BY trie le résultat et LIMIT conserve le nombre de lignes demandé.

La base de données peut organiser son exécution interne autrement.

Placer chaque condition au bon niveau

WHERE SUM(amount) > 200 échoue parce que le total n’existe pas encore à l’étape du filtrage des lignes. Une condition sur les lignes comme region = 'East' peut être évaluée avant le regroupement et appartient à WHERE.

Suivre les deux filtres

SELECT region, COUNT(*) AS orders, SUM(amount) AS total FROM orders WHERE amount >= 60 GROUP BY region HAVING SUM(amount) > 200;

Retire d’abord les commandes de 45 et de 30. East contient maintenant 120 et 95, North contient 200 et 150, et West contient 80 et 60. Calcule ensuite les totaux de 215, 350 et 140. Enfin, conserve les totaux supérieurs à 200. Le résultat est East avec deux commandes et 215, et North avec deux commandes et 350. La région NULL n’a plus de lignes et ne forme donc jamais de groupe.

Ne pas changer la question en déplaçant un filtre

« Pour chaque région, calculer la moyenne des seules commandes de 60 ou plus » utilise WHERE amount >= 60. La moyenne de East devient 107,5 à partir de 120 et 95.

HAVING MIN(amount) >= 60 demande les régions où chaque montant non NULL atteint le seuil. East est retiré parce que son minimum est de 45 ; North et West restent avec leurs commandes initiales. Le déplacement du test a changé le sens : au lieu de sélectionner des commandes individuelles, il accepte ou rejette des régions entières.

Pour ce rapport régional, region <> 'West' donne le même résultat dans WHERE ou dans HAVING : East et North restent, et le groupe NULL ne satisfait pas non plus la comparaison. WHERE énonce directement la restriction sur les lignes et l’applique avant le regroupement. Pour conserver aussi les régions manquantes, la condition doit comprendre explicitement OR region IS NULL.

Les alias SELECT ne sont pas disponibles dans les clauses WHERE et HAVING antérieures dans PostgreSQL. SQLite permet davantage d’utilisations des alias, mais écrire la colonne d’entrée ou l’expression en entier rend l’étape visée plus claire.

La division de deux nombres entiers peut supprimer la partie fractionnaire avant même l’affichage de la réponse. Cela compte lorsque tu construis une moyenne ou un pourcentage à partir de totaux et d’effectifs.

Utiliser un opérande décimal avant la division

Dans SQLite et PostgreSQL, 7 / 2 renvoie 3. Avec des entrées entières, SUM(quantity) / COUNT(*) sur la table des commandes renvoie 2 : la division de 19 articles par huit commandes perd sa partie fractionnaire. Dans PostgreSQL, SUM d’une colonne INTEGER renvoie BIGINT, ce qui donne encore une division entière dans ce cas.

Écris 1.0 * SUM(quantity) / COUNT(*) pour obtenir 2,375 dans les deux moteurs. La multiplication par 1,0 crée un opérande décimal avant la division. SUM(quantity) / COUNT(*) * 1.0 donne 2.0 parce que la division a déjà supprimé la partie fractionnaire. Les opérations de même priorité sont évaluées de gauche à droite.

Pour cette table, AVG(quantity) donne directement 2,375 dans SQLite et PostgreSQL. La formule écrite à la main est utile lorsque tu as besoin d’un dénominateur différent ou d’un pourcentage.

Calculer un pourcentage

Trois des huit commandes ont un montant d’au moins 100. Le nombre de ces commandes divisé par le nombre de toutes les commandes mesure leur part. Multiplie par 100,0 :

SELECT 100.0 * COUNT(CASE WHEN amount >= 100 THEN 1 END) / COUNT(*) AS pct_large FROM orders;

Le résultat est de 37,5. Utiliser l’entier 100 à la place produit 37 dans SQLite et pour une division de comptages entiers dans PostgreSQL. Un arrondi ultérieur ne changerait que l’affichage de 37.

Une requête sur toute la table peut ne trouver aucune ligne correspondante. NULLIF(COUNT(*), 0) renvoie NULL lorsque le comptage vaut zéro : 100.0 * COUNT(discount) / NULLIF(COUNT(*), 0) renvoie donc NULL pour une entrée vide. Un dénominateur nul sans protection provoque une erreur dans PostgreSQL ; SQLite renvoie NULL. Une part NULL peut clairement représenter « aucune commande à mesurer ».

Vérifier la base de données et le type d’entrée

Dans SQL Server, utilise AVG(quantity * 1.0) ou convertis les entrées en DECIMAL avant de calculer la moyenne. Dans PostgreSQL, SUM d’une colonne BIGINT renvoie NUMERIC : sa division ne se comporte donc pas nécessairement comme celle de SUM d’une colonne INTEGER. MySQL renvoie DECIMAL pour les sommes d’entrées entières.

Une conversion explicite peut aussi créer l’opérande décimal nécessaire : utilise CAST(SUM(quantity) AS REAL) dans SQLite ou CAST(SUM(quantity) AS NUMERIC) dans PostgreSQL. La conversion en NUMERIC de SQLite peut conserver un entier : cette conversion seule ne garantit donc pas une division décimale.

Distinguer les décimaux exacts des approximations

Les types à virgule flottante stockent des approximations binaires. Dans PostgreSQL, NUMERIC et DECIMAL prennent en charge les valeurs décimales exactes ; les additions en virgule flottante peuvent afficher de petits écarts comme 0.30000000000000004. L’affinité NUMERIC de SQLite ne permet pas un stockage décimal exact. Pour obtenir des totaux monétaires exacts dans SQLite, stocke les centimes sous forme d’entiers, additionne-les et divise par 100,0 pour l’affichage.

L’arrondi détermine le nombre de chiffres affichés dans un résultat. Garde toutes les décimales pendant les calculs jusqu’à l’étape finale, afin que les choix d’affichage ne modifient pas la mesure calculée.

Arrondir le résultat d’un agrégat

ROUND(x, n) arrondit à n chiffres après la virgule. ROUND(x) arrondit à l’entier. Par exemple, ROUND(3.14159, 2) vaut 3,14.

SELECT region, ROUND(AVG(amount), 2) AS avg_amount FROM orders GROUP BY region; donne 86,67 pour East, 175 pour North, 70 pour West et 30 pour la région NULL. SQLite peut afficher 175.0 tandis que le résultat NUMERIC de PostgreSQL affiche 175.00. Ces résultats représentent le même nombre. Arrondir un nombre et mettre en forme un libellé textuel de largeur fixe sont deux opérations différentes.

Arrondir après l’addition ou le calcul de la moyenne

Pour trois frais de 0,44, additionne d’abord pour obtenir 1,32, puis arrondis à une décimale pour obtenir 1,3. ROUND(SUM(fee), 1) suit cette séquence. SUM(ROUND(fee, 1)) arrondit chaque frais à 0,4 puis les additionne, ce qui donne 1,2. Un arrondi précoce a modifié le total.

De même, ROUND(100 * 1 / 3, 1) donne 33.0 dans SQLite et PostgreSQL parce que la division entière a déjà produit 33. ROUND(100.0 * 1 / 3, 1) conserve la partie fractionnaire et donne 33.3. Choisis le type de calcul avant d’arrondir.

La somme des parts arrondies peut ne pas être égale à 100 %. Trois parts égales peuvent chacune s’afficher sous la forme 33,3 %, soit un total de 99,9 %. Les parts sous-jacentes couvrent toujours l’ensemble. Recalculer une part arbitraire pour imposer le total affiché modifie le sens du nombre affiché.

Utiliser un type d’entrée pris en charge

PostgreSQL prend en charge ROUND(value, n) à deux arguments pour NUMERIC. Si temp est de type DOUBLE PRECISION, écris ROUND(CAST(AVG(temp) AS NUMERIC), 2). La moyenne de 20,5, 21,25 et 19,0 est de 20,25, et la conversion permet d’utiliser l’arrondi à deux arguments. Avec une colonne entière amount, la moyenne dans PostgreSQL est déjà de type NUMERIC : aucune conversion n’est nécessaire.

Pour les valeurs exactes NUMERIC de PostgreSQL, les valeurs à mi-chemin sont arrondies en s’éloignant de zéro : 2,5 devient 3 et -2,5 devient -3. SQLite donne aussi 3.0 et -3.0 pour ces exemples. L’arrondi en virgule flottante de PostgreSQL dépend de la plateforme ; l’arrondi au nombre pair le plus proche est courant. L’approximation binaire peut aussi placer un décimal juste en dessous de la valeur à mi-chemin visée. Ne fais pas d’un cas particulier d’arrondi en virgule flottante une règle générale pour le calcul décimal exact.

Remarquer deux autres différences entre dialectes

PostgreSQL, MySQL, SQL Server et Oracle utilisent un nombre négatif de décimales pour arrondir à gauche de la virgule. ROUND(1234.5, -2) donne 1 200. SQLite traite un second argument négatif comme zéro et donne 1235.0. Pour arrondir aux centaines dans SQLite, utilise ROUND(x / 100.0) * 100.

La conversion en entier dépend aussi de la base de données : SQLite supprime la partie fractionnaire, donc 2,9 devient 2 ; PostgreSQL l’arrondit à 3. Utilise ROUND lorsque le but est d’arrondir. TRUNC(x, n) dans PostgreSQL supprime des chiffres : TRUNC(2.97, 1) vaut 2.9, tandis que son arrondi à une décimale donne 3.0.

L’agrégation conditionnelle permet à différentes mesures d’utiliser différentes contributions tout en conservant le même ensemble global de lignes. Une expression CASE détermine la contribution de chaque ligne à un comptage ou à une somme.

Compter les lignes qui remplissent la condition

COUNT(CASE WHEN amount >= 100 THEN 1 END) compte les commandes d’au moins 100. L’expression renvoie 1 pour une correspondance et NULL pour les autres lignes, car elle n’a pas de ELSE. COUNT ignore ces NULL, ce qui laisse trois correspondances dans la table des commandes.

SUM(CASE WHEN amount >= 100 THEN 1 ELSE 0 END) compte les correspondances en additionnant des uns et des zéros. En présence de lignes en entrée, le résultat est le même. Ce n’est pas le cas de COUNT(CASE WHEN amount >= 100 THEN 1 ELSE 0 END) : 1 et 0 sont tous deux non NULL, donc cette expression compte les huit commandes.

Additionner les montants correspondants

Pour obtenir une somme conditionnelle, renvoie le montant lui-même :

SELECT region, COUNT(*) AS orders, COUNT(CASE WHEN amount >= 100 THEN 1 END) AS large_orders, SUM(CASE WHEN amount >= 100 THEN amount ELSE 0 END) AS large_total FROM orders GROUP BY region;

East compte trois commandes, dont une importante, pour un total de commandes importantes de 120. North compte deux commandes, toutes deux importantes, pour un total de 350. West compte deux commandes, aucune commande importante et un total de commandes importantes de zéro. Le groupe de région NULL compte une commande et affiche zéro pour les deux mesures relatives aux commandes importantes.

Le résultat conserve West parce que ses lignes d’entrée existent toujours. Ajouter WHERE amount >= 100 retirerait les lignes de West avant le regroupement, ce qui ferait disparaître West. Ce filtre ferait aussi compter uniquement les commandes importantes par COUNT(*), supprimant la comparaison avec l’ensemble des commandes.

Utiliser le bon dénominateur

Dans chaque région, calcule le pourcentage de commandes importantes avec ROUND(100.0 * COUNT(CASE WHEN amount >= 100 THEN 1 END) / COUNT(*), 1) AS pct_large. East donne 33,3, North 100,0, West 0,0 et le groupe NULL 0,0. Chaque dénominateur est le nombre de commandes du groupe concerné, et non celui des huit commandes de la table entière.

Pour un rapport sans regroupement qui peut ne contenir aucune ligne, protège le dénominateur avec NULLIF(COUNT(*), 0). Un comptage sur une entrée vide vaut zéro, mais un pourcentage sur une population vide n’a pas de dénominateur.

Créer plusieurs colonnes de catégories

Un seul résultat peut afficher un comptage par région :

SELECT COUNT(CASE WHEN region = 'East' THEN 1 END) AS east, COUNT(CASE WHEN region = 'West' THEN 1 END) AS west, COUNT(CASE WHEN region = 'North' THEN 1 END) AS north FROM orders;

Les effectifs sont trois, deux et deux. La commande d’Eli ne satisfait aucun de ces tests sur les régions nommées. Ajoute COUNT(CASE WHEN region IS NULL THEN 1 END) pour compter la commande dont la région est manquante. Comparer une région NULL à un nom ne produit pas une condition vraie.

Reconnaître d’autres syntaxes

PostgreSQL et SQLite depuis la version 3.30.0 prennent en charge COUNT(*) FILTER (WHERE amount >= 100). Le filtre s’applique à cet agrégat : un COUNT(*) voisin sans filtre compte donc toujours toutes les commandes. MySQL et SQL Server ne prennent pas en charge cette syntaxe d’agrégat avec FILTER ; la forme avec CASE fonctionne dans les deux.

SQLite et MySQL autorisent aussi SUM(amount >= 100) parce que les comparaisons produisent des valeurs de vérité numériques. PostgreSQL rejette SUM appliqué à des booléens. Utilise la forme avec CASE lorsqu’une requête doit fonctionner sur ces différents moteurs.

Une requête de synthèse part d’une question sur une population à un niveau de détail donné. Traduis ces choix avant d’écrire le code SQL, puis vérifie que le résultat répond toujours à la question initiale.

Construire le rapport dans l’ordre

Pose la question : « Pour chaque mois de 2026 comptant au moins trois commandes, combien de commandes ont été passées et quel était leur montant moyen, à deux décimales près ? »

1. Les mesures sont COUNT(*) et AVG(amount).
2. La granularité, c’est-à-dire le sens d’une ligne de résultat, est un mois du calendrier.
3. La condition sur les lignes conserve les dates de 2026.
4. La condition sur les groupes exige au moins trois commandes.
5. Le résultat doit être trié par mois, avec la moyenne arrondie pour l’affichage.

Dans SQLite :

SELECT strftime('%Y-%m', order_date) AS month, COUNT(*) AS orders, ROUND(AVG(amount), 2) AS avg_amount FROM orders WHERE order_date >= '2026-01-01' AND order_date < '2027-01-01' GROUP BY strftime('%Y-%m', order_date) HAVING COUNT(*) >= 3 ORDER BY month;

Pour les valeurs de date PostgreSQL, remplace l’expression du mois aux deux endroits par to_char(order_date, 'YYYY-MM'). La plage de dates inclut le début de 2026 et exclut le début de 2027.

Janvier compte deux commandes et ne remplit pas la condition sur les groupes. Février compte trois commandes pour un total de 305, avec une moyenne de 101,67. Mars en compte trois pour un total de 275, avec une moyenne de 91,67. Les groupes conservés représentent six commandes, et non huit, car HAVING a retiré janvier.

Vérifier avant de filtrer les groupes

Exécute d’abord le rapport sans HAVING. La somme des effectifs de tous les groupes doit être égale au nombre de lignes obtenu avec le même filtre WHERE, et la somme des totaux doit être égale au total sans regroupement. Ici, ces vérifications donnent huit et 780. Ne compare pas les totaux des groupes conservés avec l’ensemble de l’entrée après l’exclusion de certains groupes.

Cherche un groupe NULL, examine l’ordre des résultats et recalcule un groupe en filtrant la table initiale. Pour East, WHERE region = 'East' doit donner trois commandes pour un total de 260. Un écart peut révéler une colonne de regroupement supplémentaire, un filtre sur les lignes mal placé ou une expression devenue NULL.

Distinguer la commande moyenne du client moyen

AVG(amount) vaut 97,5 par commande. La dépense moyenne par client porte sur une autre unité : les cinq totaux par client sont 165, 140, 350, 95 et 30. Leur moyenne est de 156.

Calcule-la en deux étapes :

SELECT AVG(total) AS avg_customer_total FROM (SELECT customer, SUM(amount) AS total FROM orders GROUP BY customer) AS per_customer;

La requête interne crée une ligne par client. La requête externe calcule la moyenne de ces lignes, chaque client contribuant une fois. Tous les clients étant nommés, 1.0 * SUM(amount) / COUNT(DISTINCT customer) donne le même résultat de 156. Si les nombres de commandes sont inégaux, la moyenne des montants des commandes correspond à un calcul différent.

Synthétiser les groupes eux-mêmes

Pour compter les clients ayant passé plus d’une commande, liste-les d’abord, puis compte leurs lignes :

SELECT COUNT(*) FROM (SELECT customer FROM orders GROUP BY customer HAVING COUNT(*) > 1) AS repeat_customers;

La réponse est trois. Imbriquer directement des agrégats, comme dans MAX(AVG(amount)) à un même niveau de requête, échoue dans SQLite et PostgreSQL. Pour trouver la région ayant la moyenne la plus élevée, trie les moyennes groupées : ORDER BY AVG(amount) DESC LIMIT 1 renvoie North avec 175. Oracle prend aussi en charge les fonctions de groupe imbriquées, mais un niveau de requête distinct rend les deux calculs explicites. Vérifie les ex aequo lorsqu’un rapport demande un premier résultat.