SQL1c.LID - Les agrégats

Il est parfaitement possible de faire des calculs avec des champs, avant de les afficher ou de les utiliser dans des comparaisons.
Les opérateurs de calculs sont les opérateurs mathématiques classiques plus quelques fonctions qui permettent d'extraire des bouts de chaîne de caractères (subsrting) ou de date (year, month ou day).

Le plus simple est de voir quelques exemples :

Quel est ?

Le Prix des articles Hors Taxes (HT) et Toutes Taxes Comprises (TTC = HT+20% <=> HT*1.20)

Remarquer que le calcul est renommé en 'prixTTCArt' avec 'AS'

SELECT refArt, prixHTArt, (prixHTArt * 1.2) AS prixTTCArt
FROM article ;

Quel est ?

Le Montant des lignes des articles de la commande 1 ?

SELECT contenir.refArt, (prixHTArt * qty) AS montantHTArt
  FROM contenir
       JOIN article ON contenir.refArt = article.refArt ;

Quel est ?

La référence articles dont le montant commandé dans la commande 1 est supérieir à 100€ ?

SELECT contenir.refArt 
  FROM contenir
       JOIN article ON contenir.refArt = article.refArt 
 WHERE prixHTArt * qty > 100 ;

1. Compter les lignes d'une requête

Comment savoir quel est le nombre de clients ?

> Simple, on compte le nombre de lignes de la requête qui donne la liste des clients :

SELECT count(*) FROM Client ;

C'est tout.

2. Autres agrégats

• sum() : somme des valeurs
• avg() : moyenne des valeurs (avg=average)
• min() : valeur mini de la liste
• max() : valeur maxi de la liste

Exemples

-- Somme des quantités des articles de la commande 1
SELECT sum(qty) FROM contenir WHERE numCde = 1
 
-- Moyenne des prix des articles
SELECT avg(prixHTArt) FROM article
 
-- Prix TTC maximum des articles avec un taux de TVA de 20%
SELECT max(prixHTArt * 1.2) FROM article
 
-- Quantité minimum commandée
SELECT min(qty) FROM contenir
 
-- Date de la commande la plus récente
SELECT max(dateCde) FROM commande

Jusque là, tout va bien ?

Si on veut faire des tous totaux, on va être obligé d'ajouter une nouvelle clause : GROUP BY pour avoir des "ruptures" de regroupements.
Analysons les exemples suivants :

1. Compter les lignes d'une requête selon un critère

Comment savoir quel est le nombre de commandes par client ?
--> Simple, on compte le nombre de lignes de la table commande, groupée par numéro de client.

Pour faire ceci, on va afficher le numéro de client ET le comptage. Cependant, il est alors OBLIGATOIRE d'ajouter la clause GROUP BY qui reprend le numéro de client :

SELECT idClient, count(*) FROM Client GROUP BY idClient ;

Noter que si il y a d'autres champs qui ne sont pas des agrégats : comptages, sommes, etc..., il FAUT les mettre dans GROUP BY.

2. Autres exemples

-- Somme des quantités des articles par numéro de commande
SELECT numCde, sum(qty) FROM contenir GROUP BY numCde ;
 
-- Moyenne des prix des articles par type article
SELECT typaArt, avg(prixHTArt) FROM article GROUP BY typeArt
 
-- Quantité minimum commandée par article
SELECT refArt, min(qty) FROM contenir GROUP BY refArt
 
-- Nombre de clients par ville ?
SELECT nomVille, count(*) FROM client GROUP BY nomVille

Et enfin, quelle est la quantité commandée par référence et description d'article ?

• la table de base est l'association contenir,
• on aura une jointure vers l'article pour obtenir sa description
• comme on a la référence et la description dans SELECT, il y aura 2 champs dans Group By

SELECT refArt, descArt, sum(qty)
  FROM contenir 
  JOIN article ON contenir.refArt=article.refArt
 GROUP BY refArt, descArt

La requête la plus emblématique de ce cas est celle où on calcule le CA d'un client pour une période donnée (juste pour compliquer la chÔse).
Ici, la question est : Quel est le CA et le nombre d'articles par commande, du client SAI en janvier 2004 ?
On aura besoin des données suivantes pour faire les calculs :

• le CA est la somme, pour une commande, des quantités multipliées par le prix de chaque article
• le nombre d'articles pour une commande est le comptage des ligne contenir appartenant à la commande.
• la date des commandes pour sélectionner (restreindre) la période.

Pour faire joli et répondre à la question, on va indiquer le numéro et le nom du client, le numéro de commande le CA de chaque commande puis le nombre d'articles.
Il nous faut les tables suivantes (dans l'ordre) :

• contenir pour la quantité et le nombre d'articles de la commande,
• article pour le prix,
• commande pour le numéro et les dates de la période,
• et client pour son nom,

Et faire les jointures qui vont bien ...
La totale, quoi!
Voyons ci-dessous : la chÔse.

SELECT client.id, nomCli, numCde, SUM(qty*prixHTArt) AS montantHTArt, COUNT(*) AS nbreArt
 
  FROM contenir
  JOIN commande ON commande.numCde = commande.numCde -- jointure n° 1 : contenir-commande
  JOIN client   ON commande.id = client.idClient     -- jointure n° 2 : commande-client
  JOIN article  ON contenir.refArt = article.refArt  -- jointure n° 3 : contenir-article
 
 WHERE YEAR(dateCde) = 2004  --  les cdes de 2004,
   AND MONTH(dateCde)= 02   --  et de février,
   AND client.nomCli = "SAI" --  pour le client nommé SAI,
 
GROUP BY client.id, nomCli, numCde -- le groupement se fait sur tous les champs non agrégés.

Avec un peu de rigueur, ce type de requêtes est assez facile à construire.

Parfois, la condition de restriction de WHERE ne suffit pas et on aimerait faire une restriction sur le résultat d'un agrégat (count, sum ou avg).
Or on ne peut pas utiliser d'agrégat dans WHERE car la restriction (WHERE) traite les enregistrement des tables AVANT la projection (SELECT champs).

SQL résoud ce problème en utilisant la clause HAVING qui permet de faire des sélections APRES le choix des champs dans l'exécution de la requête.

Voici trois exemples pour comprendre :

1. Exemple détaillé

Quels sont les clients qui ont moins de 10 commandes en 2004 ?
A priori, c'est un comptage des commandes groupé par numéro de client.

SELECT numClient, count(*) as NbCde 
  FROM commande
 WHERE YEAR(dateCde) = 2004 
 GROUP BY numClient

Cependant, ici, on affiche tous les clients sans distinction des résultats. Il faut faire une sélection sur un comptage (count(*)>2)or on ne peut pas utiliser COUNT() dans WHERE**,

Tentons la requête suivante qui renvoie une erreur !!!

SELECT numClient, count(*) AS NbCde 
  FROM commande
 WHERE YEAR(dateCde) = 2004 
   AND COUNT(*) < 10    -- ERREUR , pas de count() dans WHERE, le champ projeté est un calcul 
 GROUP BY numClient

Noter que la requête suivante renvoie aussi une erreur !!!

SELECT numClient, count(*) AS NbCde 
  FROM commande
 WHERE YEAR(dateCde) = 2004 
   AND NbCde(*) < 10    -- ERREUR , pas de count() dans WHERE, le champ projeté n'existe pas dans les tables
 GROUP BY numClient

Il faut impérativement utiliser HAVING pour ajouter une sélection sur le comptage

SELECT nomVille, count(*) FROM client 
 WHERE YEAR(dateCde) = 2004 
HAVING COUNT(*) < 10  -- ici c'est bon, on fait une restriction sur le résultat de la projection
 GROUP BY nomVille

2. Exemple

Afin de faire une analyse de zone de chalandise, on souhaiterai savoir quelles sont les villes dont le nombre de clients est supérieur à 1 ?
C'est un comptage groupé par ville avec HAVING pour ajouter une restriction sur le comptage

SELECT nomVille, count(*) FROM client 
HAVING COUNT(*) > 1 
GROUP BY nomVille

3ème. Exemple

Effectuons la même chose avec le chiffre d'affaire 2004 des clients : quels sont les numéro des clients qui ont un chiffre d'affaire supérieur à 1000EUR ?
Calculons le CA par numCli :

• on a 3 tables, donc 2 jointures,
• plus un calcul

Pas de problème, on suit le chemin des clés étrangères vers les clés primaires entre les tables et on fait toutes les jointures

SELECT numCli, sum(qty*prixHTArt) AS CAHTCli
  FROM contenir
  JOIN commande ON contenir.numCde = commande.numCde -- prmière jointure
  JOIN article  ON contenir.refArt = article.refArt -- seconde jointure
  WHERE YEAR(dateCde) = 2004 -- la restriction normale
HAVING sum(qty*prixHTArt) > 1000 -- la restriction supplémentaire
GROUP BY numCde

Ouf, c'est fini ! mais si tout est compris, le niveau est déjà très honorable.
Il faut maintenant battre le fer tant qu'il est chaud.