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).
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 ?
| count(*) |
| 714 |
Exemple : combien y a-t-il d'élèves en 1TSIG ?
| count(*) |
| 29 |
Remarque : cela revient au même de compter (*), (usr_id) ou n'importe quel autre champ. Par exemple :
| count(usr_id) |
| 29 |
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 :
| count(usr_special) |
| 29 |
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 ?
| count(statlog_ip) |
| 31191 |
Avec le DISTINCT : combien d'adresses IP différentes ont été utilisées pour les connexions au site ?
| count(distinct statlog_ip) |
| 4337 |
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 :
Donnez le nombre de clients qui habitent Avignon :
Donnez le nombre de catégories de logiciels différentes achetées par Nicolas PUECH (résultat attendu : 4) :
Exemple : quelle est la note au TP n°69 (Allociné) pour l'élève ahsaini ?
| SUM(tp_note) | count(*) |
| 0 |
Cette fonction sera pratique pour compter des chiffres d'affaires, des sommes de quantités (pour gérer les stocks par exemple), etc.
Exemple avec MIN et MAX : dates minimales et maximales de connexion au site ?
| MIN(statlog_date) | MAX(statlog_date) |
| 2018-11-26 09:21:37 | 2023-06-07 13:57:51 |
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) ?
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 ;
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) ?
| nombre élèves |
| 714 |
Nombre d'élèves par section (on compte après regroupement sur la section) ?
| section | nombre eleves |
| 0 | 617 |
| 2 | 37 |
| 6 | 29 |
| 10 | 31 |
Nombre de réponses au TP par elève (on compte après regroupement sur l'identifiant de l'élève) ?
| usr_login | nombre de reponses |
| abaud | 144 |
| acantone | 159 |
| aechchalyouat | 92 |
| ahsaini | 93 |
| egiraud | 115 |
| jsalvador | 123 |
| jssick | 61 |
| lbegue | 137 |
| lbianciotto | 319 |
| ... | ... |
Le GROUP BY permet de répondre à de nombreuses questions intéressantes, par exemple :
SELECT client_id, sum(Montant_vente) FROM ventes GROUP BY client_id ;SELECT date_visite, count(visites) FROM statistiques_de_visites GROUP BY date_visite ;SELECT catégorie, count (annonce) FROM annonces GROUP BY catégorie ;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.
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
Donnez le nombre de clients par ville (ville, nombre de clients) :
Donnez le nombre de produits achetés par chaque client (nom, prénom, nombre de produits achetés) :
Donnez le nombre d'achat par produit (Nom du produit, nombre d'achat) :
Donnez le nombre d'achat par produit et par mois (Nom du produit, mois et année, nombre d'achat) :
Pour chaque client, donnez la date de son dernier achat (nom, prénom, date) :
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) :
Liste des clients qui ont acheté plus d'un logiciel :
Liste des clients qui ont acheté des produits dans une seule catégorie :
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' :
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 :
| concat(usr_first_name, ' ', usr_name) | usr_tp_note |
| Noah VOYER | 93.3 |
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).
Quel est le dernier produit qui a été acheté par Nicolas PUECH (afficher le nom du produit, et la date de l'achat) ?
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.
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) ?
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) ?
Répondre aux questions du chapitre 4 du TP allociné.
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).