Requete sur 3 tables avec calcul de moyenne

03 mai 2006, 17:13

Bonjour à tous,

Je bloque sur la conception d'une requete.
J'ai 3 tables :

la 1ère, artistes, avec comme champs : id, nom, prenom
la 2ème, oeuvres, avec comme champs : id, artiste_id, titre
la 3ème, notes, avec comme champs : id, oeuvre_id, note

Je souhaiterais récuperer la somme des moyennes des notes des oeuvres de chaque artiste, par exemple :

L'artiste Toto à 2 oeuvres, pour son oeuvre 1 il y a 4 notes et pour son oeuvre 2 il y a 3 notes. Je voudrais donc :
1/ faire la moyenne des notes de chaque oeuvre
2/ additionner la moyenne des 2 oeuvres
3/ trier les résultats en fonction du total des moyennes.

J'ai bidouiller des trucs genre

Code : Tout sélectionner

SELECT artistes.id, artistes.nom, oeuvres.titre, AVG(notes.note)
FROM artistes, oeuvres, notes
WHERE oeuvres.artiste_id = artistes.id
AND notes.oeuvre_id = oeuvres.artiste_id
GROUP BY artistes.id

mais ça ne donne pas vraiment le résultat attendu, vu que j'ai un tri en fonction des moyennes j'ai l'impression...

J'ai également essayé de calculer une somme de moyenne

Code : Tout sélectionner

SELECT SUM(AVG(notes.note))

mais j'ai une erreur de syntaxe

Any idea ?

03 mai 2006, 18:18

Bon en fait c'est bon je me suis demerder autrement avec la magnifique fonction sortOn de Flash

03 mai 2006, 18:31

Dommage, j'avais même créé un petit jeu d'essai pour trouver une solution, mais il y a une des données que tu devras obtenir par programmation à partir du résultat obtenu.
Le résultat est le suivant:
-1- trié par oeuvre :

Code : Tout sélectionner

mysql> SELECT a.prenom, a.nom, o.titre, AVG(n.note) AS moyenne
    -> FROM artistes AS a, oeuvres AS o, notes AS n
    -> WHERE a.id = o.artiste_id
    -> AND o.id = n.oeuvre_id
    -> GROUP BY a.prenom, a.nom, o.titre
    -> ORDER BY a.id, o.id;
+---------+--------+-----------+---------+
| prenom  | nom    | titre     | moyenne |
+---------+--------+-----------+---------+
| Jean    | Dupont | oeuvre 1  | 4.2500  |
| Jean    | Dupont | oeuvre 2  | 12.0000 |
| Jean    | Dupont | oeuvre 3  | 9.5000  |
| Jean    | Dupont | oeuvre 4  | 11.0000 |
| Jules   | Durand | oeuvre 5  | 9.2500  |
| Jules   | Durand | oeuvre 6  | 11.2500 |
| Jules   | Durand | oeuvre 7  | 12.6000 |
| Jacques | Duplat | oeuvre 8  | 12.2500 |
| Jacques | Duplat | oeuvre 9  | 8.8000  |
| Jacques | Duplat | oeuvre 10 | 12.6000 |
| Jacques | Duplat | oeuvre 11 | 9.6667  |
| Jacques | Duplat | oeuvre 12 | 14.2500 |
+---------+--------+-----------+---------+
12 rows in set (0.00 sec)

-2- le même mais trié par moyennes en ordre croissant:

Code : Tout sélectionner

mysql> SELECT a.prenom, a.nom, o.titre, AVG(n.note) AS moyenne
    -> FROM artistes AS a, oeuvres AS o, notes AS n
    -> WHERE a.id = o.artiste_id
    -> AND o.id = n.oeuvre_id
    -> GROUP BY a.prenom, a.nom, o.titre
    -> ORDER BY a.id, moyenne;
+---------+--------+-----------+---------+
| prenom  | nom    | titre     | moyenne |
+---------+--------+-----------+---------+
| Jean    | Dupont | oeuvre 1  | 4.2500  |
| Jean    | Dupont | oeuvre 3  | 9.5000  |
| Jean    | Dupont | oeuvre 4  | 11.0000 |
| Jean    | Dupont | oeuvre 2  | 12.0000 |
| Jules   | Durand | oeuvre 5  | 9.2500  |
| Jules   | Durand | oeuvre 6  | 11.2500 |
| Jules   | Durand | oeuvre 7  | 12.6000 |
| Jacques | Duplat | oeuvre 9  | 8.8000  |
| Jacques | Duplat | oeuvre 11 | 9.6667  |
| Jacques | Duplat | oeuvre 8  | 12.2500 |
| Jacques | Duplat | oeuvre 10 | 12.6000 |
| Jacques | Duplat | oeuvre 12 | 14.2500 |
+---------+--------+-----------+---------+
12 rows in set (0.00 sec)

Mais à partir de là, il est facile de récupérer la somme des moyennes par artiste...

03 mai 2006, 19:08

Aaah jen étais pas si loin en fait

Si je prend ton exemple, il n'y a donc pas de possibilité en requete SQL d'additionner la moyenne des oeuvre de Jean Dupont alors ?

Au début, je pensais m'en sortir en effectuant plusieurs requetes, en combinant les résultats dans un tableau et en triant le tableau mais je crois que j'aurais sécher pareil sur le tri du tableau

A la base je voulais faire ça pour créer un fichier XML que j'envoie à Flash. Mais entre-temps j'ai trouvé la fonction Flash sortOn() qui me fait ça naturellement.

Du coup maintenant j'ai simplement ça :

Code : Tout sélectionner

function getArtistes() {
	$res = $db->get_results('SELECT id, prenom, nom FROM artistes');
	foreach ($res as $key=>$row) {
	   $avgNote = $this->getAvgNotes($row->id);
		$this->xml .= '<artiste prenom="'.utf8_encode(htmlspecialchars($row->prenom)).'" nom="'.utf8_encode(htmlspecialchars($row->nom)).'" avgNote="'.$avgNote.'">';
		$this->xml .= '</artiste>';
		}
	}

function getAvgNotes($artiste_id) {
	$avg_artiste = '';
	if($img = $db->get_results('SELECT id FROM oeuvres WHERE artiste_id='.$artiste_id)) {
		foreach($img as $row) {
	   	if($avg_image = $db->get_var('SELECT AVG(note) FROM notes WHERE oeuvre_id='.$row->id)) {
	   		$avg_artiste += $avg_image;
	   		}
	   	}
	   }
	return round($avg_artiste, 2);
	}

qui me sort

Code : Tout sélectionner

<artiste prenom="Sniper" nom="Wolf" avgNote="6.25"/>
<artiste prenom="Psycho" nom="Mantis" avgNote="3.5"/>
<artiste prenom="Liquid" nom="Snake" avgNote="8.12"/>

Dans Flash, j'ai plu qu'a faire un

Code : Tout sélectionner

monTab.sortOn("avgNote", Array.DESCENDING | Array.CASEINSENSITIVE);
monTab.sortOn("nom", Array.DESCENDING | Array.CASEINSENSITIVE);

03 mai 2006, 19:10

Ben si tu veux la moyenne générale pour un artiste ou la moyenne par oeuvre, les deux sont faisables, mais pas en une seule requête, mais comme je t'ai dit, à partir de la première, faire la moyenne du total par artiste devient un jeu d'enfant.

Requete sur 3 tables avec calcul de moyenne

Qui est en ligne