Comment utiliser GROUP BY et ORDER BY dans la même requête : Guide détaillé

Démêler comment utiliser deux clauses SQL de base - GROUP BY et ORDER BY - dans une même requête.

GROUP BY et ORDER BY sont des clauses SQL de base. En tant que telles, elles sont enseignées relativement tôt dans le processus d'apprentissage. Et elles ne constituent généralement pas un obstacle majeur pour les débutants. Cependant, l'utilisation conjointe de GROUP BY et ORDER BY peut être source de confusion. À la fin de cet article, votre confusion à ce sujet aura disparu.

Si vous souhaitez vous entraîner à utiliser GROUP BY avec ORDER BY, notre cours interactif vous aidera. SQL pour les débutants vous aidera. Comme son nom l'indique, il couvre les bases de SQL. Il facilite le suivi des sujets que nous allons présenter dans l'article.

Le cours lui-même comporte 129 exercices permettant de mettre en pratique les concepts SQL de base. Ces concepts comprennent l'extraction de données d'une ou de plusieurs tables (JOIN) et le filtrage des données à l'aide de WHERE et d'opérateurs tels que LIKE, ILIKE, IN et BETWEEN. Bien sûr, GROUP BY est là, tout comme les opérations d'ensemble UNION, INTERSECT, et EXCEPT.

Que sont GROUP BY et ORDER BY en SQL ?

GROUP BY et ORDER BY ne sont pas la même chose - c'est évident !

La clause SQL GROUP BY regroupe les lignes de données ayant les mêmes valeurs dans les colonnes spécifiées. Cette clause est le plus souvent utilisée avec les fonctions d'agrégation SQL pour calculer des statistiques (telles que le décompte de certaines valeurs, la somme, la moyenne et la valeur minimale/maximale d'un ensemble) pour un groupe de lignes. Voici un exemple simple :

SELECT 
  country,
  COUNT(*)
FROM box_office
GROUP BY country;

La requête demande à la base de données de regrouper les données en fonction des valeurs de la colonne country. Elle affiche ensuite le nom du pays et compte les lignes. Cela nous permettra de savoir combien d'enregistrements de la table appartiennent à chaque pays.

Parlons maintenant de la commande ORDER BY. Cette commande permet de trier les résultats de la requête dans l'ordre croissant (de 1 à 10, de A à Z) ou décroissant (de 10 à 1, de Z à A). Le tri ascendant est la valeur par défaut ; si vous omettez le mot-clé ASCending ou DESCending, la requête sera triée dans l'ordre ascendant. Vous pouvez spécifier l'ordre de tri en utilisant ASC ou DESC. Voici un exemple simple :

SELECT 
  movie,
  city,
  gross
FROM box_office
ORDER BY gross;

Cette requête sélectionne le nom du film, la ville où il est projeté et les recettes brutes. Les résultats sont ensuite triés par recettes brutes à l'aide de la clause ORDER BY.

C'est quelque chose que vous connaissez probablement. Voyons maintenant comment ces clauses SQL peuvent être utilisées ensemble. Il peut être utile d'avoir à portée de main cet article sur le fonctionnement de GROUP BY ou le guide sur ORDER BY, au cas où vous auriez besoin d'éclaircissements sur un point mentionné dans cet article.

Exemple de données

Nous utiliserons une table nommée box_office dans les exemples suivants. Vous pouvez la créer à l'aide de cette requête.

Il s'agit d'un aperçu des recettes au box-office de deux films : "Beau Is Afraid" et "The French Dispatch".

Les données montrent les recettes (en dollars américains) des cinémas de deux pays - l'Espagne et les Pays-Bas - et de leurs villes respectives : Amsterdam, Rotterdam, La Haye, Barcelone, Madrid et Valence.

GROUP BY et ORDER BY d'une colonne non agrégée dans SELECT

Commençons par l'exemple le plus simple d'utilisation de GROUP BY et ORDER BY dans une requête. Nous voulons afficher la liste des films par ordre alphabétique.

Voici comment grouper et ordonner le résultat en fonction d'une colonne :

SELECT movie
FROM box_office
GROUP BY movie
ORDER BY movie ASC; 

La requête sélectionne le nom du film dans la table box_office. Nous devons ensuite utiliser GROUP BY. Sans cela, le résultat serait constitué des douze lignes du tableau, montrant les noms répétitifs des deux mêmes films du tableau box_office. Nous voulons que chaque film n'apparaisse qu'une seule fois, car c'est là tout l'intérêt de la liste. Le regroupement des données s'effectue simplement en spécifiant la colonne par laquelle vous souhaitez regrouper les données dans la clause GROUP BY.

La sortie est ordonnée à l'aide de la clause ORDER BY. Comme pour GROUP BY, nous spécifions le nom de la colonne dans la clause. Nous voulons que le résultat soit trié par ordre alphabétique, le nom de la colonne est donc suivi de ASC.

Utilisons cette image de l'ordre d'exécution de la requête SQL pour visualiser comment la base de données parvient au résultat.

La requête récupère d'abord la table dans la clause FROM. Elle regroupe ensuite les données en fonction de la colonne spécifiée dans la clause GROUP BY. Ensuite, elle sélectionne la colonne dans l'instruction SELECT. Ce n'est qu'après tout cela que le résultat est trié par la clause ORDER BY.

En d'autres termes, les données sont d'abord regroupées, puis triées. Cela nous amène à une remarque importante : lorsque vous utilisez GROUP BY et ORDER BY dans une même requête, GROUP BY doit toujours, sans exception, être écrit avant ORDER BY!

Jetons un coup d'œil au résultat de la requête.

Comme vous pouvez le constater, il s'agit d'une liste présentant nos deux films par ordre alphabétique.

GROUP BY et ORDER BY une colonne agrégée dans SELECT

Vous pouvez utiliser GROUP BY en même temps que ORDER BY lorsque vous ordonnez par une fonction agrégée présentée dans SELECT. Voici un exemple :

SELECT 
  movie,
  SUM(gross) AS gross_per_movie
FROM box_office
GROUP BY movie
ORDER BY gross_per_movie DESC;

La requête sélectionne un film dans la table. Nous utilisons ensuite la fonction d'agrégation SUM() pour additionner les recettes brutes du film. Comme nous voulons afficher les recettes brutes par film, nous devons regrouper les résultats par nom de film. Enfin, nous voulons trier les résultats du plus élevé au plus bas. Nous pouvons le faire en listant la colonne agrégée dans ORDER BY. Celle-ci doit être suivie du mot-clé DESC pour trier du plus élevé au plus bas.

Notez que nous utilisons l'alias gross_per_movie pour la colonne agrégée dans ORDER BY. Nous aurions également pu utiliser l'expression agrégée complète :

SELECT 
  movie,
  SUM(gross) AS gross_per_movie
FROM box_office
GROUP BY movie
ORDER BY SUM(gross) DESC;

Voici à quoi ressemble le résultat :

Il montre deux films et leurs recettes dans l'ordre décroissant.

GROUP BY et ORDER BY d'une colonne non agrégée pas dans SELECT

Dans le premier exemple, nous avons ordonné le résultat en fonction de la colonne indiquée dans SELECT. Essayons maintenant d'ordonner les données en fonction d'une colonne qui ne figure pas dans SELECT:

SELECT SUM(gross) AS gross_by_city
FROM box_office
GROUP BY city
ORDER BY city ASC;

Dans cette requête, nous utilisons la fonction SUM() pour calculer les revenus bruts par ville. Cependant, il n'y a pas de colonne "ville" dans SELECT. C'est parce que nous voulons seulement voir les valeurs brutes. Nous regroupons tout de même les résultats par ville, comme nous le ferions si cette colonne se trouvait dans SELECT. Enfin, nous classons les résultats par ordre alphabétique de ville.

Le résultat est présenté ci-dessous :

Comme vous pouvez le voir, la requête fonctionne. N'oubliez pas : vous pouvez ordonner le résultat en fonction d'une colonne non agrégée qui ne figure pas dans SELECT - mais uniquement si cette colonne est répertoriée dans GROUP BY.

Cela signifie que les données peuvent également être regroupées en fonction d'une colonne qui n'apparaît pas dans SELECT. La colonne peut-elle figurer dans SELECT mais pas dans GROUP BY? Non ! Vous devez vous souvenir de la règle suivante : si la colonne non groupée apparaît dans SELECTelle doit également apparaître dans GROUP BY.

GROUP BY et ORDER BY d'une colonne agrégée qui n'apparaît pas dans SELECT

Modifions un peu l'exemple ci-dessus et essayons d'ordonner les données en fonction d'une colonne agrégée qui ne figure pas dans SELECT.

Voici le code. Il tente d'afficher une liste de villes classées en fonction de leurs revenus bruts :

SELECT city
FROM box_office
GROUP BY city
ORDER BY SUM(gross);

Nous sélectionnons la colonne city et regroupons les résultats en fonction de celle-ci.

Nous utilisons ensuite SUM() dans ORDER BY afin de pouvoir trier les résultats en fonction des revenus par ville. Nous effectuons un tri croissant.

Voyons si la requête fonctionne :

Oui, elle fonctionne ! Autre remarque : vous pouvez classer les résultats en fonction d'une colonne agrégée qui n'apparaît pas dans le fichier SELECT.

GROUP BY et ORDER BY plusieurs colonnes

Jusqu'à présent, nous avons regroupé les données en fonction d'une seule colonne. Voyons comment vous pouvez regrouper des données en fonction de plusieurs colonnes.

Montrons chaque ville et son pays :

SELECT 
  city,
  country,
  AVG(gross) AS average_gross
FROM box_office
GROUP BY city, country
ORDER BY city;

Nous sélectionnons la ville et le pays. Ensuite, nous utilisons AVG() pour calculer le salaire brut moyen par ville. Nous listons ensuite les mêmes colonnes sur GROUP BY. Comme vous l'avez appris, chaque colonne apparaissant dans SELECT doit également apparaître dans GROUP BY. Lorsque nous énumérons plusieurs colonnes dans GROUP BY, nous les séparons simplement par une virgule.

Enfin, nous trions les résultats par ville et par ordre croissant :

Cela soulève la question suivante : Si vous pouvez GROUP BY plusieurs colonnes, pouvez-vous également ORDER BY plus d'une colonne ?

Essayons :

SELECT city,
	 country,
	 AVG(gross) AS average_gross
FROM box_office
GROUP BY city, country
ORDER BY country, city;

Le principe est le même que pour GROUP BY: il suffit d'énumérer les colonnes dans ORDER BY et de les séparer par une virgule.

Voici ce que la requête renvoie :

Oui, le résultat est vraiment ordonné par le pays et ensuite par chaque ville à l'intérieur du pays.

Colonne agrégée dans GROUP BY et ORDER BY

Nous avons essayé toutes les combinaisons, mais nous n'avons pas essayé de GROUP BY et ORDER BY plusieurs colonnes, dont l'une est agrégée. Essayons donc :

SELECT 
  city,
  AVG(gross) AS average_gross_by_city
FROM box_office
GROUP BY city, AVG(gross)
ORDER BY city, AVG(gross);

Cette requête veut afficher le revenu brut moyen par ville. Nous listons la ville dans SELECT et utilisons la fonction AVG(). Ensuite, nous groupons par ville et par la colonne agrégée. Enfin, le résultat est trié par la même colonne.

Cependant, le seul résultat que nous obtenons est cette erreur.

Le message d'erreur signifie littéralement ce qu'il dit : les fonctions d'agrégation ne sont pas autorisées dans GROUP BY. Quel serait même l'intérêt de les utiliser dans GROUP BY? La fonction d'agrégation regroupe les données sur une ligne et GROUP BY ne peut rien faire de plus.

Utilisation de GROUP BY avec HAVING et ORDER BY

GROUP BY et ORDER BY ne sont pas les seules clauses qui peuvent être utilisées dans une requête - nous pouvons également ajouter HAVING.

Examinons le code suivant pour voir comment cela fonctionne.

SELECT 
  city,
  SUM(gross) AS gross_per_city
FROM box_office
GROUP BY city
HAVING SUM(gross) > 800000
ORDER BY SUM(gross) > 800000 DESC;

Cette requête doit

• Retourner les villes dont les revenus bruts sont supérieurs à 800 000 $.
• Afficher le montant gagné par cette ville.
• Classer les résultats par ordre décroissant des revenus bruts.

Pour ce faire, nous sélectionnons la colonne ville et utilisons la fonction SUM(). Nous regroupons ensuite les résultats par ville. Ensuite, nous utilisons la clause HAVING pour n'afficher que les villes dont les revenus sont supérieurs à 800 000 $. Nous utilisons simplement la colonne aggregate de SELECT et la comparons à la condition à l'aide de l'opérateur de comparaison '>'. Ensuite, nous utilisons la même chose dans ORDER BY pour trier les résultats par ordre décroissant.

La règle est la suivante : La clause HAVING est toujours écrite après GROUP BY mais avant ORDER BY.

Vous pouvez revoir l'image de l'ordre d'exécution des requêtes SQL pour vérifier ce point. Vous verrez que HAVING est exécuté après GROUP BY mais avant ORDER BY. En d'autres termes, les données sont d'abord regroupées, puis filtrées et enfin ordonnées.

Voici le résultat de la requête :

Vous voyez qu'il n'y a que deux villes où nos deux films ont rapporté plus de 800 000 $ : Amsterdam et Madrid.

Pratique de l'utilisation de GROUP BY avec ORDER BY

Cet article vous a montré que, oui, GROUP BY et ORDER BY peuvent être utilisés dans la même requête SQL. Non seulement cela, mais ils peuvent être utilisés de plusieurs façons.

En cours de route, nous avons appris quelques règles importantes :

1. GROUP BY vient avant ORDER BY.
2. La sortie peut être ordonnée par une colonne non agrégée qui n'apparaît pas dans SELECT uniquement si la colonne apparaît dans GROUP BY.
3. Les données peuvent être regroupées par la colonne non agrégée qui n'apparaît pas dans SELECT.
4. Toute colonne non agrégée apparaissant dans SELECT doit être répertoriée dans GROUP BY.
5. La sortie peut être ordonnée par une colonne agrégée qui n'apparaît pas dans SELECT.
6. Une colonne agrégée n'est pas autorisée dans GROUP BY.
7. HAVING vient après GROUP BY mais avant ORDER BY.

Bien que nous ayons expliqué ces règles et que vous ayez l'impression d'avoir appris quelque chose, cela ne suffit pas.

Pour retenir ces connaissances, vous devez vous entraîner à utiliser GROUP BY et ORDER BY - surtout au début de votre parcours SQL. S'entraîner en SQL signifie écrire beaucoup de code. C'est la seule façon de consolider vos connaissances. Notre cours interactif SQL pour les débutants propose 129 exercices SQL pratiques, ce qui laisse beaucoup de place à la pratique !