Exercices sur les agrégats

1
2

SELECT COUNT(*) 
FROM Race;

Simple échauffement.

1
2
3
4

SELECT COUNT(pere_id)
FROM Animal
INNER JOIN Espece ON Espece.id = Animal.espece_id
WHERE Espece.nom_courant = 'Chien';

L'astuce ici était de ne pas oublier de donner la colonne pere_id en paramètre à COUNT(), pour ne compter que les lignes où pere_id est non NULL. Si vous avez fait directement WHERE espece_id = 1 au lieu d'utiliser une jointure pour sélectionner les chiens, ce n'est pas bien grave.

1
2
3
4
5

SELECT nom_courant AS Espece, AVG(Race.prix) AS prix_moyen
FROM Race
INNER JOIN Espece ON Race.espece_id = Espece.id
WHERE Espece.nom_courant IN ('Chat', 'Chien')
GROUP BY Espece.nom_courant WITH ROLLUP;

Ne pas oublier WITH ROLLUP pour avoir le résultat général.

1
2
3
4
5

SELECT sexe, COUNT(*), GROUP_CONCAT(nom SEPARATOR ', ')
FROM Animal
INNER JOIN Espece ON Animal.espece_id = Espece.id
WHERE nom_courant = 'Perroquet amazone'
GROUP BY sexe;

Il suffisait de se souvenir de la méthode GROUP_CONCAT() pour pouvoir réaliser simplement cette requête. Peut-être avez-vous groupé sur l'espèce aussi (avec nom_courant ou autre). Ce n'était pas nécessaire puisqu'on avait restreint à une seule espèce avec la clause WHERE. Cependant, cela n'influe pas sur le résultat, mais sur la rapidité de la requête.

1
2
3
4
5

SELECT Race.nom, COUNT(Animal.race_id) AS nombre
FROM Race
LEFT JOIN Animal ON Animal.race_id = Race.id
GROUP BY Race.nom
HAVING nombre = 0;

Il fallait ici ne pas oublier de faire une jointure externe (LEFT ou RIGHT, selon votre requête), ainsi que de mettre la colonne Animal.race_id (ou Animal.id, ou Animal.espece_id mais c'est moins intuitif) en paramètre de la fonction COUNT().

1
2
3
4
5
6

SELECT Espece.nom_latin, COUNT(espece_id) AS nombre
FROM Espece
LEFT JOIN Animal ON Animal.espece_id = Espece.id
WHERE sexe = 'M' OR Animal.id IS NULL
GROUP BY Espece.nom_latin
HAVING nombre < 5;

À nouveau, une jointure externe et espece_id en argument de COUNT(), mais il y avait ici une petite subtilité en plus. Puisqu'on demandait des informations sur les mâles uniquement, il fallait une condition WHERE sexe = 'M'. Mais cette condition fait que les lignes de la jointure provenant de la table Espece n'ayant aucune correspondance dans la table Animal sont éliminées également (puisque forcément, toutes les colonnes de la table Animal, dont sexe, seront à NULL pour ces lignes). Par conséquent, il fallait ajouter une condition permettant de garder ces fameuses lignes (les espèces pour lesquelles on n'a aucun individu, donc aucun mâle). Il fallait donc ajouter OR Animal.id IS NULL, ou faire cette condition sur toute autre colonne d'Animal ayant la contrainte NOT NULL, et qui donc ne sera NULL que lors d'une jointure externe, en cas de non-correspondance avec l'autre table. Il n'y a plus alors qu'à ajouter la clause HAVING pour sélectionner les espèces ayant moins de cinq mâles.

1
2
3
4
5
6

SELECT Animal.sexe, Race.nom, Espece.nom_courant, COUNT(*) AS nombre
FROM Animal
INNER JOIN Espece ON Animal.espece_id = Espece.id
INNER JOIN Race ON Animal.race_id = Race.id
WHERE Animal.sexe IS NOT NULL
GROUP BY Espece.nom_courant, Race.nom, sexe WITH ROLLUP;

Deux jointures sont nécessaires pour pouvoir afficher les noms des races et des espèces. Il suffit alors de ne pas oublier l'option WITH ROLLUP et de mettre les critères de regroupement dans le bon ordre pour avoir les super-agrégats voulus.

1
2
3
4
5
6

SELECT Espece.nom_courant, SUM(COALESCE(Race.prix, Espece.prix)) AS somme
FROM Animal
INNER JOIN Espece ON Espece.id = Animal.espece_id
LEFT JOIN Race ON Race.id = Animal.race_id
WHERE Animal.nom IN ('Parlotte', 'Spoutnik', 'Caribou', 'Cartouche', 'Cali', 'Canaille', 'Yoda', 'Zambo', 'Lulla')
GROUP BY Espece.nom_courant WITH ROLLUP;

C'est ici la fonction SUM() qu'il fallait utiliser, puisqu'on veut le prix total par groupe. Sans oublier le WITH ROLLUP pour avoir également le prix total tous groupes confondus. Quant au prix de chaque animal, c'est typiquement une situation où l'on peut utiliser COALESCE() !