SQL Server
GROUP BY expliqué dans SQL Server
Apprenez à créer une ligne de synthèse par statut, client ou combinaison avec GROUP BY dans SQL Server.
Dans cet article
Dans la leçon précédente, COUNT et SUM résumaient l’ensemble des lignes en une seule ligne. Pourtant, on veut souvent davantage de détail : combien de commandes pour chaque statut, ou quel montant chaque client a dépensé ? Les agrégats répondent à « quel est le total ? ». GROUP BY répond à « quel est le total pour chaque groupe ? ». Il définit le grain du résultat, c’est-à-dire ce que représente une ligne de sortie.
Une ligne par statut
La base de démonstration contient huit commandes et quatre statuts : Complete, Pending, Shipped et Processing. Regroupons par statut et comptons les lignes :
SELECT
Status,
COUNT(*) AS OrderCount
FROM dbo.Orders
GROUP BY Status;SQL Server place ensemble les lignes ayant la même valeur Status, puis renvoie une ligne par statut distinct. Complete compte 4 commandes, Pending en compte 2, Shipped en compte 1 et Processing en compte 1. Il y a quatre lignes de résultat, pas huit : chacune représente un groupe de statut. Sans ORDER BY, SQL Server ne garantit pas leur ordre d’affichage.
- Orders : 8 lignes
- GROUP BY Status
- Complete : 4
- Pending : 2
- Shipped : 1
Calculer un agrégat par groupe
GROUP BY accompagne souvent une fonction d’agrégation. Cette requête calcule le montant des commandes de chaque statut :
SELECT
Status,
SUM(Amount) AS TotalSales
FROM dbo.Orders
GROUP BY Status;Les sommes connues sont 449.49 pour Complete, 335.00 pour Pending, 82.75 pour Shipped et NULL pour Processing, dont le seul montant est inconnu. La fonction est calculée séparément sur chaque groupe. COUNT(*) compte ses lignes et SUM(Amount) additionne ses montants. Les résultats correspondent exactement aux huit lignes de dbo.Orders.
On peut aussi regrouper par client afin d’obtenir une ligne pour chaque client ayant au moins une commande :
SELECT
CustomerId,
COUNT(*) AS OrderCount,
SUM(Amount) AS Amount
FROM dbo.Orders
GROUP BY CustomerId;La requête renvoie quatre lignes : CustomerId 1, 2, 4 et 7. Ana (1) a 3 commandes pour un total de 250.49 ; Ben (2), 1 commande de 310.00 ; David (4), 2 commandes totalisant 281.75 ; Grace (7), 2 commandes dont les montants connus totalisent 25.00. Les clients sans commande n’apparaissent pas : aucune ligne de Orders ne forme leur groupe.
Inclure les clients sans commande
Pour obtenir aussi les clients sans commande, partez de Customers et utilisez LEFT JOIN :
SELECT
c.CustomerId,
c.FirstName,
c.LastName,
COUNT(o.OrderId) AS OrderCount,
SUM(o.Amount) AS Amount
FROM dbo.Customers AS c
LEFT JOIN dbo.Orders AS o
ON c.CustomerId = o.CustomerId
GROUP BY
c.CustomerId,
c.FirstName,
c.LastName;Les colonnes client apparaissent dans GROUP BY, car elles identifient la ligne souhaitée pour chaque client. COUNT(o.OrderId) compte les commandes correspondantes et vaut zéro en l’absence de commande : après la jointure, OrderId vaut NULL et COUNT(colonne) ignore NULL. SUM(o.Amount) vaut alors NULL, puisqu’il n’existe aucun montant non NULL à additionner. Si le rapport doit afficher zéro, COALESCE(SUM(o.Amount), 0) remplace ce résultat NULL par zéro. COALESCE renvoie la première expression non NULL ; les données sources restent inchangées.
Règle pratique : une expression sélectionnée qui n’est pas agrégée doit généralement figurer dans GROUP BY. SQL Server doit savoir quelle valeur afficher pour chaque groupe. Sélectionner un nom client tout en regroupant uniquement par Status serait ambigu : un groupe de statut peut contenir plusieurs clients. Regroupez les colonnes qui définissent la ligne de synthèse voulue.
Définir le grain avant de choisir les colonnes
Avant d’écrire la requête, formulez ce que représente une ligne. Le grain du résultat, aussi évoqué dans la leçon sur les lignes répétées après JOIN, peut être : une ligne par statut (GROUP BY Status), par client (GROUP BY CustomerId et attributs affichés), ou par client et statut (GROUP BY CustomerId, Status).
Regrouper plusieurs colonnes crée des groupes correspondant à leurs combinaisons :
SELECT
Status,
CustomerId,
COUNT(*) AS OrderCount
FROM dbo.Orders
GROUP BY
Status,
CustomerId;Chaque ligne correspond désormais à une combinaison client-statut. Si un client a des commandes de deux statuts, il peut donc apparaître deux fois. Ajouter une colonne de regroupement affine le grain et augmente souvent le nombre de groupes. Trop peu de colonnes peuvent réunir des faits qu’il fallait séparer ; trop de colonnes produisent une synthèse excessivement détaillée.
GROUP BY, DISTINCT et tri
DISTINCT supprime les combinaisons dupliquées parmi les colonnes sélectionnées. GROUP BY forme des groupes, généralement pour y calculer des agrégats. Une requête qui sélectionne uniquement Status avec DISTINCT peut lister les statuts ; une requête groupée avec COUNT(*) peut également compter leurs commandes. Ces formes se ressemblent pour certains résultats simples, mais elles ne sont pas interchangeables dans tous les cas.
Pour trier une synthèse, ajoutez ORDER BY :
SELECT
Status,
SUM(Amount) AS Amount
FROM dbo.Orders
GROUP BY Status
ORDER BY Amount DESC;Les trois totaux sont affichés du plus élevé au plus faible. ORDER BY change l’ordre de présentation, pas les groupes.
Raisonner sur une requête groupée
Partez de la question plutôt que d’une liste de colonnes. Définissez d’abord le grain : par exemple, une ligne par client. Identifiez ensuite la clé qui le définit, comme CustomerId. Choisissez les mesures à calculer pour chaque clé : nombre de commandes, montant total, ou les deux. Enfin, déterminez si une condition filtre les données et s’il faut conserver les clients sans commande. Cette démarche évite d’ajouter des colonnes qui changent discrètement le sens de chaque ligne.
Pour un résumé par statut, Status est la clé de regroupement et chaque ligne représente un statut. Pour les totaux client, la clé est CustomerId. Le nom du client décrit cette clé ; si vous l’affichez, regroupez les colonnes du nom pour que SQL Server sache quelle valeur associer à chaque groupe. CustomerId est unique dans le jeu de démonstration : FirstName et LastName identifient donc le même client dans chaque groupe. Dans d’autres modèles, ne supposez pas qu’une colonne affichée est liée à une clé unique sans garantie du schéma.
Vérifiez un résultat groupé à l’aide des données sources. Les comptes par statut doivent totaliser les huit commandes : 4 + 2 + 1 + 1 = 8. Les montants doivent donner 867.24 : 449.49 + 335.00 + 82.75 = 867.24. Ce contrôle est utile sur ce petit jeu. Si les sommes ne correspondent pas, vérifiez WHERE, les relations de jointure et les colonnes de regroupement. Un chiffre plausible dans chaque groupe ne prouve pas que les lignes attendues ont été retenues.
LEFT JOIN modifie les données disponibles pour le regroupement. Il conserve chaque client et fournit NULL dans les colonnes commande en l’absence de correspondance. COUNT(o.OrderId) ne compte que les identifiants de commandes ; COUNT(*) compte également la ligne client conservée. Pour un client sans commande, les résultats diffèrent : zéro contre un. SUM sur le côté commande absent renvoie NULL, puisqu’aucun montant non NULL n’a été fourni. N’utilisez COALESCE que si zéro convient au sens du rapport. « Aucun total enregistré » et « total connu égal à zéro » ne désignent pas toujours la même chose.
Les colonnes de regroupement doivent correspondre au niveau de détail demandé. Ajouter OrderDate à un résumé client crée un groupe par client et par date : le résultat n’est plus une ligne par client. FirstName est généralement redondant si CustomerId détermine le client, mais doit figurer dans GROUP BY si la requête l’affiche. Ne regroupez pas une mesure telle que Amount uniquement parce qu’elle est affichée : cela séparerait les commandes de montants différents en plusieurs groupes pour un même client.
Dans une requête simple, DISTINCT et GROUP BY peuvent tous deux produire la liste des statuts uniques, mais pensez à l’usage prévu. Si vous ne voulez que des libellés uniques, DISTINCT exprime directement cette demande. Si vous voulez un compte ou un total par libellé, GROUP BY définit les groupes sur lesquels calculer l’agrégat. Aucun des deux ne corrige une mauvaise clé de jointure ou un grain mal défini. Choisissez d’abord le sens des lignes, puis l’opération SQL qui l’exprime.
Une bonne habitude consiste à écrire une phrase avant la requête : « une ligne par client, avec le nombre et le total de ses commandes ». Après exécution, comparez cette phrase aux colonnes obtenues et vérifiez un cas connu dans les tables. Ici, Ana doit avoir trois commandes pour 250.49. Si elle apparaît dans plusieurs lignes alors que le rapport promet une ligne par client, une colonne de regroupement a probablement affiné le grain. Si elle manque, vérifiez que la requête part bien de Customers lorsque les clients sans commande doivent être conservés.
Erreurs fréquentes et exercices
Évitez de sélectionner une colonne non agrégée absente de GROUP BY, ou de regrouper trop ou trop peu de colonnes. Ne confondez pas le grain : n’ajoutez pas GROUP BY simplement pour masquer des valeurs répétées après une jointure. Décidez d’abord si vous voulez un détail ou une synthèse. Avec LEFT JOIN, comptez une clé facultative du côté droit, par exemple COUNT(o.OrderId), et non COUNT(*) : la ligne client conservée existe même sans commande. La somme est alors NULL, sauf si COALESCE est appliqué. Consultez le comportement de LEFT JOIN pour savoir quelles lignes conserver.
Exercices : (1) compter les commandes par statut ; (2) calculer les ventes par statut ; (3) compter et additionner par client ; (4) afficher chaque client, même sans commande, avec son nombre de commandes ; (5) décrire le grain d’un regroupement par Status et CustomerId.
Réponses
-- 1 : Complete 4, Pending 2, Shipped 1, Processing 1
SELECT Status, COUNT(*) AS OrderCount FROM dbo.Orders GROUP BY Status;
-- 2 : Complete 449.49, Pending 335.00, Shipped 82.75, Processing NULL
SELECT Status, SUM(Amount) AS Sales FROM dbo.Orders GROUP BY Status;
-- 3 : une ligne par client ayant commandé
SELECT CustomerId, COUNT(*) AS OrderCount, SUM(Amount) AS Amount
FROM dbo.Orders GROUP BY CustomerId;
-- 4 : dix lignes, dont six clients sans commande
SELECT c.CustomerId, COUNT(o.OrderId) AS OrderCount
FROM dbo.Customers AS c LEFT JOIN dbo.Orders AS o ON c.CustomerId = o.CustomerId
GROUP BY c.CustomerId;Une fois les groupes formés, on peut les filtrer. Continuez avec WHERE ou HAVING pour distinguer le filtrage des lignes d’entrée du filtrage des groupes.
La feuille de route Foundations présente le parcours débutant complet et les leçons disponibles.
Vérifiez ce que vous avez retenu
2 questions rapides
Fondamentaux de SQL Server
Leçon 14 sur 25
Ce contenu vous a-t-il été utile ?
Que pourrions-nous améliorer ?
Merci — cela m’aide à améliorer Javi Builds.
Continuer à apprendre
- Aide-mémoireActivité par login, hôte et application dans SQL ServerRegroupez l’activité SQL Server par login, hôte, application et base de données pour identifier le client ou la charge de travail à l’origine des connexions, des requêtes, de la consommation CPU, des lectures ou des blocages.Ouvrir l’aide-mémoire
- Feuille de routeSQL Server FoundationsApprenez à créer une ligne de synthèse par statut, client ou combinaison avec GROUP BY dans SQL Server.Voir la feuille de route
Que devrais-je créer ensuite ?
Je développe Javi Builds autour des problèmes concrets rencontrés par les data engineers. Dites-moi quel sujet vous aimeriez voir ensuite.
Restez au courant
Recevez uniquement les nouvelles de Javi Builds qui vous intéressent.
Consultez votre boîte de réception
Je vous ai envoyé un lien de confirmation. Votre abonnement sera actif après confirmation.