SQL SELECT : les regroupements.
Réponse AJAX

Les fonctions d'agrégations : COUNT, SUM, MIN, MAX, AVG

Elles permettent d'afficher un seul résultat global, au lieu d'afficher chaque enregistrement. Elles agrègent les résultats en faisant une opération mathématique qui porte sur tous les enregistrements (qui correspondent à la restriction du WHERE).

COUNT : compte le nombre de lignes

Si je ne mets pas de condition (pas de WHERE), je compte l'ensemble des enregistrements dans une table. Par exemple :
Combien y a-t-il de comptes d'utilisateurs sur www.bts-sio.com ?

SELECT count(*) from user
count(*)
594
exécuté en 0 sec.

Exemple : combien y a-t-il d'élèves en 1TSIG ?

SELECT count(*) from user where usr_special & 4;
count(*)
36
exécuté en 0.001 sec.

Remarque : cela revient au même de compter (*), (usr_id) ou n'importe quel autre champ. Par exemple :

SELECT count(usr_id) from user where usr_special & 4;
count(usr_id)
36
exécuté en 0.001 sec.

Ce qui suit, même si cela donne le même résultat, je le compterai faux. Puisqu'on compte des utilisateurs, il est plus logique de mettre un champ équivalent dans le count :

SELECT count(usr_special) from user where usr_special & 4;
count(usr_special)
36
exécuté en 0.001 sec.

Remarque : count peut accepter la clause DISTINCT, pour compter le nombre de valeurs différentes. Exemple :
Sans le DISTINCT : combien d'adresses IP ont été utilisées pour les connexions au site ?

SELECT count(statlog_ip) from cours_stat_login ;
count(statlog_ip)
6111
exécuté en 0 sec.

Avec le DISTINCT : combien d'adresses IP différentes ont été utilisées pour les connexions au site ?

SELECT count(distinct statlog_ip) from cours_stat_login;
count(distinct statlog_ip)
1016
exécuté en 0.019 sec.

Attention : vous devez tester vos requêtes avant de les poster ici. Elle ne doivent contenir aucune erreur de syntaxe. Pour les tester, installez la base de données dans PHPMyAdmin.

Donnez le nombre de clients dans la base :

Votre réponse n°1

Donnez le nombre de clients qui habitent Avignon :

Votre réponse n°2

Donnez le nombre de catégories de logiciels différentes achetées par Anthony PUECH (résultat attendu : 4) :

Votre réponse n°3

SUM : calcule la somme des valeurs d'une colonne

Exemple : quelle est la note au TP n°69 (Allociné) pour l'élève vbourgues ?

SELECT SUM(tp_note), count(*) from cours_tp_rep where tp_id=69 and tp_usr_id = 567 ;
SUM(tp_note)count(*)
0
exécuté en 0.059 sec.

Cette fonction sera pratique pour compter des chiffres d'affaires, des sommes de quantités (pour gérer les stocks par exemple), etc.

MIN, MAX : renvoie les valeurs minimales et maximales dans une colonne

Exemple avec MIN et MAX : dates minimales et maximales de connexion au site ?

SELECT MIN(statlog_date), MAX(statlog_date) FROM cours_stat_login;
MIN(statlog_date)MAX(statlog_date)
2018-11-26 09:21:372019-08-20 22:57:51
exécuté en 0.006 sec.

Quel est le plus grand identifiant de client (NumC) dans la base (requête utile si on n'a pas mis le champ en numéro automatique) ?

Votre réponse n°4

AVG : calcule la moyenne des valeurs d'une colonne

Moins utilisé. Il peut servir par exemple à calculer la moyenne des notes d'un élève. Exemple fictif :
SELECT avg(note) FROM notes_eleve WHERE eleve_id = 1 ;

La clause GROUP BY

Principe

Elle permet d'appliquer des fonctions d'agrégations non pas sur l'ensemble des résultats, mais après avoir regroupé sur un champ donné.

Cela permettra, par exemple, de calculer des résultats par classe (nombre d'élèves par classe), par région (chiffre d'affaires par région), par jour (nombre de connexions par jour), etc.

Par exemple :
Nombre d'élèves total dans la base (on compte sur l'ensemble de la table) ?

SELECT count(*) as "nombre élèves" FROM user ;
nombre élèves
594
exécuté en 0 sec.

Nombre d'élèves par section (on compte après regroupement sur la section) ?

SELECT usr_special & 14 as section, count(*) as "nombre eleves"
FROM user
GROUP BY usr_special & 14 ;
sectionnombre eleves
0519
214
436
825
exécuté en 0.001 sec.

Nombre de réponses au TP par elève (on compte après regroupement sur l'identifiant de l'élève) ?

SELECT usr_login, count(*) as "nombre de reponses"
FROM user, cours_tp_rep
WHERE usr_id = tp_usr_id AND usr_special & 4
GROUP BY usr_login ;
usr_loginnombre de reponses
aprats153
fbricchi248
fmarin252
gtembouret190
hbas159
itassan156
jbertrand164
jrakel229
kpotrel213
......
exécuté en 0.701 sec.

Le GROUP BY permet de répondre à de nombreuses questions intéressantes, par exemple :

Vous remarquez que quand la formulation en français de la question est : "compter ceci par cela", alors on a GROUP BY cela dans la requête.

Quelques précisions

On peut regrouper sur plusieurs critères, par exemple : nombre de ventes par département et par mois. Chiffre d'affaires par département et par commercial.

On peut regrouper sur une expression, par exemple GROUP BY month(date).

Le GROUP BY provoque un dommage collatéral : il trie. Je compterai faux à ceux qui se servent du GROUP BY uniquement pour trier, car pour ça il existe ORDER BY.

Lorsqu'on met des valeurs dans le SELECT (hors fonctions d'agrégation), on doit forcément les retrouver dans le GROUP BY. Par exemple :
SELECT a, b, sum(c), d, count(e) FROM ... GROUP BY a, b, d ; => c'est juste
SELECT f, g, count(h) FROM ... GROUP BY f ; => ça ne marche pas car g figure dans le SELECT mais pas dans le GROUP BY.
"Mais monsieur, ça marche quand même sous MySQL !" => je m'en fous.

Au contraire, on peut mettre des choses dans le GROUP BY qui ne sont pas dans le SELECT. Par exemple :
SELECT nom, prénom, count(*) FROM ... GROUP BY id, nom, prénom => c'est juste

On ne peut pas à la fois compter les élèves et afficher le nom de chacun, c'est paradoxal (c'est faux conceptuellement et techniquement). Par exemple :
SELECT section, count(*) as nb_élèves, nom FROM ... GROUP BY section => c'est pas possible

Exercices

Donnez le nombre de clients par ville (ville, nombre de clients) :

Votre réponse n°5

Donnez le nombre de produits achetés par chaque client (nom, prénom, nombre de produits achetés) :

Votre réponse n°6

Donnez le nombre d'achat par produit (Nom du produit, nombre d'achat) :

Votre réponse n°7

Donnez le nombre d'achat par produit et par mois (Nom du produit, mois et année, nombre d'achat) :

Votre réponse n°8

Pour chaque client, donnez la date de son dernier achat (nom, prénom, date) :

Votre réponse n°9

La clause HAVING

Elle permet de faire une condition sur une valeur issue d'une fonction d'agrégat. Le principe est simple :

Par exemple : Sections qui ont moins de 24 élèves (j'affiche, en plus, le nombre d'élèves dans ces sections) :

SELECT usr_special & 14 as section, count(*) as "nombre élèves"
FROM user
GROUP BY usr_special & 14
HAVING count(*) < 24 ;
sectionnombre élèves
214
exécuté en 0.001 sec.

Liste des clients qui ont acheté plus d'un logiciel :

Votre réponse n°10

Liste des clients qui ont acheté des produits dans une seule catégorie :

Votre réponse n°11

Liste des clients qui n'ont pas acheté depuis fin 2010, avec HAVING (car c'est faisable avec NOT IN aussi). La formulation est : liste des clients dont la date de la dernière commande est inférieure au '2011-01-01' :

Votre réponse n°12

Qui est le dernier / le premier / le plus / le moins ...
select ... where champ = (select regroupement(...) ...)

Maintenant nous allons voir comment répondre à une requête du genre :

Par exemple, pour cette dernière question, la requête SQL qui donne le résultat est :

SELECT concat(usr_first_name, ' ', usr_name), usr_tp_note
FROM user
WHERE usr_special & 4
AND usr_tp_note = (SELECT MAX(usr_tp_note) FROM user WHERE usr_special & 4) ;
concat(usr_first_name, ' ', usr_name)usr_tp_note
Peter Landriau91.5
exécuté en 0.001 sec.

Donnez le prénom et le nom du tout premier client du magasin (affichez prénomC, nomC, et la date de sa première facture).

Votre réponse n°13

Quel est le dernier produit qui a été acheté par Anthony PUECH (afficher le nom du produit, et la date de l'achat) ?

Votre réponse n°14

Les regroupements de regroupements

Quel est le regroupement qui a la plus grande valeur ?

Pour certaines questions, on voudrait faire un regroupement de regroupement. Par exemple :
Quel est le plus gros chiffre d'affaires réalisé par un commercial ?

Malheureusement, cette formulation ne marche pas :

On va donc décomposer en deux requêtes. Le résultat de la première requête sera stocké dans une vue.

Requête 1 : elle calcule le chiffre d'affaires par commercial.

Requête 2 : elle affiche le maximum.

Démonstration sur les notes des TP dans la base des cours.

Qui a obtenu cette plus grande valeur ?

Cette fois-ci, on va répondre à la question :
Quel est le commercial qui a réalisé ce plus grand chiffre d'affaires ?

On garde la requête 1 et la vue qui en découle (rappel : elle calcule le chiffre d'affaires (somme de MontantTTC) par commercial).

Requête 2 : elle affiche le commercial dont le chiffre d'affaires est égal au maximum de la vue précédente.

On peut améliorer, en affichant ce fameux chiffre d'affaires maximum :

On peut améliorer, en affichant le nom du commercial grâce à une jointure avec la table commercial :

Démonstration sur les notes des TP dans la base des cours.

Quel est (sont) le(s) client(s) qui a (ont) acheté le plus de produits (affichez le prénom, puis le nom, puis le nombre de produits achetés) ?

Votre réponse n°15

Quel sont les clients les moins assidus, c'est à dire celui auquel est associé le moins de DateAchat différentes (afficher son prénom puis son nom) ?

Votre réponse n°16

Répondre aux questions du chapitre 4 du TP allociné.

Lien vers les études de cas

Etude de cas ARLE, 2011, métropole : Cas IMAGE'IN, dossier 3, question 2 (temps total passé sur le projet) (go).

Etude de cas DA, 2011, Métropole : Cas Atlantik, dossier 4, questions 1 (chiffre d'affaires d'un affrètement) et 4 (moyenne des taux de remplissage des affrètements) (go).

Etude de cas ARLE, 2010, métropole : Cas SCCP, dossier 4, question 4 A et B (quantités totales de céréales) (go).

Etude de cas DA, 2010, Nouvelle-Calédonie : Cas ASDOMI, dossier 2, questions 4 (a et b) (kilométrage par véhicule, véhicule qui a le plus roulé) (go).

Etude de cas DA, 2010, métropole : Cas NOIXCOOP, dossier 2, questions 3 (nombre de variétés d'arbres par type d'usage) (go).