[SQL] Regroupez vos champs avec GROUP BY

Cette clause qui nous permet de regrouper nos entrées en agrégats s'utilise dans les requêtes de type SELECT.
Elle se place entre la clause WHEREet la clause ORDER BY.

Pour regrouper, il faut indiquer un champ selon lequel il faut regrouper.
Par exemple, dans notre table jeux_videos, si l'on veut regrouper les jeux par possesseur, on utilisera la clause suivante :
Code : SQL
Qui aura pour effet de créer un agrégat pour possesseur = 'Corentin', un autre pour possesseur = 'Florent', et un autre pour chaque possesseur que je n'ai pas cité.

On peut également regrouper selon plusieurs champs.
Par exemple, pour créer un agrégat pour chaque console de chaque possesseur, on utilisera la clause suivante :
Code : SQL
Qui aura pour effet de créer un agrégat possesseur = 'Corentin' et <police nom="courrier">console='PC', un autre possesseur = 'Corentin' et console = 'PS2' (pas plus d'agrégats pour Corentin car il ne propose que des jeux PC/PS2), un autre pour possesseur = 'Florent' et console = 'Dreamcast', et ainsi de suite pour chaque possesseur et chaque console.

Notez que GROUP BY ordonne les agrégats à la manière d'une clause ORDER BY.
C'est-à-dire qu'en groupant par possesseur, le premier agrégat sera l'agrégat possesseur = 'Corentin' et le dernier possesseur = 'Sebastien'.
On peut aussi classer les agrégats en ordre décroissant avec le même mot-clé que dans un ORDER BY, c'est-à-dire DESC.
Ainsi, l'agrégat possesseur = 'Sebastien' sera le premier, et l'agrégat possesseur = 'Corentin' le dernier avec cette clause :
Code : SQL

Maintenant que nous savons créer ces agrégats, il serait bon de pouvoir en tirer profit.
Pour cela, il existe plusieurs fonctions que l'on peut utiliser sur chaque agrégat en présence d'une clause GROUP BY.

Compter le nombre d'entrées par agrégat

Vous connaissez déjà cette fonction puisqu'il s'agit tout simplement de COUNT().
Cette fonction retourne le nombre de valeurs qui ne valent pas NULL dans la colonne du champ passé en argument dans l'agrégat.
Notez toutefois que COUNT(*) fait office d'exception car il ne fait que compter le nombre de lignes sans vérifier les valeurs.
Exemple : sélectionner le nombre de jeux proposés par chaque possesseur :
Code : SQL
Qui retourne :

possesseur	nb_jeux
Corentin	2
Florent	17
Mathieu	3
Michel	12
Patrick	13
Sébastien	1
Sebastien	2

Ce qui signifie qu'il y a deux id ne valant pas NULL dans l'agrégat possesseur = 'Corentin', et on peut avoir le même raisonnement avec les autres agrégats.

Le ne valant pas NULL peut sembler inutile, cependant il permet dans une requête avec jointure externe de donner 0 et non 1 si la jointure ne trouve aucune entrée dans la table jointe, car dans ce cas c'est la valeur NULL qui est sélectionnée pour les entrées de cette table.

On peut aussi utiliser DISTINCT pour faire un comptage distinct.
Par exemple, si on veut compter le nombre de consoles différentes de chaque possesseur, il faudra préciser qu'on veut les consoles distinctes de l'agrégat ainsi :
Code : SQL
Ainsi la requête retourne :

possesseur	nb_consoles
Corentin	2
Florent	11
Mathieu	3
Michel	7
Patrick	6
Sébastien	1
Sebastien	2

Pour vous exercer, vous pouvez essayer d'obtenir :

• le nombre de jeux proposés par console
  Secret (cliquez pour afficher)
  Code : SQL

  1	SELECT console, COUNT(id) AS nb_jeux FROM jeux_videos GROUP BY console;

• le nombre de jeux proposés par possesseur et console
  Secret (cliquez pour afficher)
  Code : SQL

  1	SELECT possesseur, console, COUNT(id) AS nb_jeux FROM jeux_videos GROUP BY possesseur, console

Faire la moyenne des valeurs d'une colonne par agrégat

Nous allons maintenant voir la fonction AVG() (de average, moyenne en anglais).
Elle retourne la moyenne des valeurs présentes dans la colonne du champ que l'on passe en argument parmi les lignes de l'agrégat.
Bien entendu, cela ne concerne que les champs numériques.

Exemple : on veut connaître le prix moyen des jeux par console.
Code : SQL
Pour améliorer, même si ça n'a rien à voir avec le tuto, on peut arrondir le résultat à deux décimales avec ROUND(nombre, nombre_de_decimales) ainsi :
Code : SQL
Cette requête retourne :

console	prix_moyen
Dreamcast	12.67
Gameboy	5.00
Gamecube	47.50
GBA	34.50
Megadrive	9.00
NES	4.33
Nintendo 64	21.50
PC	36.33
PS	26.00
PS2	28.11
SuperNES	8.67
Xbox	48.30

Ce qui signifie que le prix moyen, qu'on a récupéré par AVG(prix), arrondi à 2 décimales est de 12.67 pour la Dreamcast.
Vérifions.
Faisons la somme des prix :
Code : SQL

prix
12
11
15

12 + 11 + 15 = 23 + 15 = 38.
Il y a 3 entrées donc divisons par 3 : 38 / 3 ~= 12.67.
On retrouve bien la moyenne.

Tiens, nous venons de faire la somme...
Intéressant, ça.

Faire la somme des valeurs d'une colonne par agrégat

Eh bien il existe une fonction spéciale pour ça, il s'agit de SUM().
SUM() retourne donc la somme de toutes les valeurs présentes dans la colonne du champ passé en argument dans l'agrégat.

Exemple : on veut connaître le prix total que l'on devrait payer si l'on achetait tous les jeux d'un possesseur, par ce possesseur.
Il s'agit de la somme des prix.
Nous utiliserons donc la requête suivante :
Code : SQL
Qui retourne :

possesseur	prix_total
Corentin	34
Florent	393
Mathieu	65
Michel	367
Patrick	448
Sébastien	49
Sebastien	61

Donc, la somme de toutes les valeurs de la colonne du champ prix dans l'agrégat possesseur = 'Corentin' vaut 34.

Sélectionner la valeur minimale / maximale d'une colonne par agrégat

Nous allons utiliser les fonctions MIN() et MAX().
MIN() retourne la valeur minimale parmi celles de la colonne du champ passé en argument dans l'agrégat, MAX() sa valeur maximale.

Exemple : on veut connaître le prix du jeu le moins cher et celui du jeu le plus cher, selon la console.
On utilisera la requête suivante :
Code : SQL
Qui retourne :

console	prix_minimal	prix_maximal
Dreamcast	11	15
Gameboy	5	5
Gamecube	40	55
GBA	25	44
Megadrive	2	15
NES	2	7
Nintendo 64	15	25
PC	15	50
PS	20	30
PS2	10	59
SuperNES	6	10
Xbox	29	60

Donc, la valeur minimale présente dans la colonne du champ prix de l'agrégat console = 'Dreamcast' est 11, sa valeur maximale est 15.

Concaténer les valeurs de la colonne d'un champ par agrégat

Nous allons ici faire de la concaténation.
Il s'agit de mettre bout à bout différentes chaînes ; ici, ce seront les valeurs présentes dans la colonne d'un champ par agrégat.
On utilisera la fonction GROUP_CONCAT(), qui s'utilisera ainsi :
Code : SQL

Cela évaluera d'abord la concaténation de tout ce qui est passé en argument par ligne, puis insérera le séparateur, qui peut être indiqué par SEPARATOR et qui est par défaut la virgule, entre les concaténations qui seront classées s'il est indiqué par le ORDER BY.
Si on indique DISTINCT, on ne gardera parmi les concaténations identiques que la première.

Un petit exemple serait le bienvenu pour la compréhension, je crois.

Exemple : on veut récupérer une chaîne de la forme Nom du jeu (prix?) pour chaque jeu, avec un retour à la ligne entre chacune de ces lignes, regroupé par console et les jeux doivent être classés par prix.
On utilisera la requête suivante :
Code : SQL
Donc : on prend nom (prix?), on ordonne selon la valeur de prix dans la ligne de l'agrégat où l'on prend la concaténation, et on sépare par un retour à la ligne (\n) les concaténations obtenues.

Ce qui donne :

console	phrase
Dreamcast	Crazy Taxi (11?) NBA 2k (12?) Sydney 2000 (15?)
Gameboy	Tetris (5?)
Gamecube	Wario World (40?) Super Smash Bros Melee (55?)
GBA	F-ZERO (25?) Pokemon Rubis (44?)
Megadrive	Sonic (2?) Street Fighter 2 (10?) Spider-Man (15?)
NES	The Rocketeer (2?) Super Mario Bros (4?) Ice Hockey (7?)
Nintendo 64	Zelda : ocarina of time (15?) Monopoly (21?) FIFA 64 (25?) Mario Kart 64 (25?)
etc...	etc...

Pas mal, non ?

Les autres fonctions d'agrégats

Nous venons de voir les principales fonctions d'agrégat, c'est-à-dire les plus utilisées.
Il en existe toutefois quelques-unes que je n'ai pas présentées.
Au programme :

• l'application d'un opérateur de bit à toutes les valeurs de la colonne du champ passé en argument
• Lla "variance standard" et la "déviation standard" des valeurs de la colonne du champ passé en argument (pour être franc, je ne sais même pas ce que c'est).

Si cela vous intéresse, je vous invite à consulter la page de la documentation réservée aux fonctions d'agrégats.

GROUP BY peut s'utiliser avec l'option WITH ROLLUP ainsi :
Code : SQL
Cette option créera à la fin de chaque "groupe d'agrégats" une entrée valant NULL pour les champs et la valeur retournée par une fonction d'agrégat appliquée à tous les champs du "groupe d'agrégats" si une fonction d'agrégats est utilisée.
C'est-à-dire que si par exemple on voulait connaître, en plus du prix minimal et maximal par console, le prix minimal et maximal global, on utiliserait la même requête que celle vue pour MIN()/MAX() mais avec l'option WITH ROLLUP, qui rajoutera une entrée ainsi :

console	prix_minimal	prix_maximal
NULL	2	60

après les autres.
En cas de GROUP BY sur plusieurs champs, l'option WITH ROLLUP créera ce genre d'entrée à chaque fin de "sous-agrégat".
C'est-à-dire qu'en classant par possesseur puis par console, on aurait un ROLLUP pour chaque possesseur puis pour l'ensemble des entrées.

Notez que l'utilisation de WITH ROLLUP rend impossible celle d'une clause ORDER BY, bien qu'on puisse toujours indiquer l'ordre d'apparition des agrégats via la clause GROUP BY elle-même.

Je vais maintenant vous présenter la clause HAVING.
Celle-ci se situe juste après la clause GROUP BY et permet, tout comme la clause WHERE, de trier vos données pour ne prendre que celles qui vous intéressent.
La différence est que dans la clause WHERE vous ne pouvez pas utiliser d'alias définis dans le SELECT.
Exemple : pour chaque jeu, il y a 2 ? de frais divers qui s'ajoutent au prix indiqué, et on veut les prix T.T.C. des jeux pour lesquels ce dernier est inférieur ou égal à 30 ?.
On utilisera la clause HAVING ainsi :
Code : SQL
Cette requête retournera :

nom	console	prix_ttc
Super Mario Bros	NES	6
Sonic	Megadrive	4
Zelda : ocarina of time	Nintendo 64	17
Mario Kart 64	Nintendo 64	27
...	...	...

Alors qu'avec une clause WHERE, on vous aurait signalé que le champ prix_ttc n'existe pas.
On peut également utiliser la clause HAVING avec les fonctions d'agrégat, c'est d'ailleurs ce qui lui vaut sa place dans ce tutoriel.
Ainsi, si on veut la liste des vendeurs vendant plus de cinq jeux, on utilisera cette requête :
Code : SQL
Qui retourne :

possesseur
Florent
Michel
Patrick

Bien entendu, on aurait pu également sélectionner le nombre de jeux et à ce moment-là, utiliser un alias dans la clause HAVING de cette manière :
Code : SQL