Bonjour !

J'ai un problème avec cette requête SQL.

Plusieurs personnes font des critiques de film et attribuent des notes, placées dans la table notes.

Si leur note est négative (erreur de notation), elle est ignorée, pas prise en compte par AVG. (d'où le WHERE)

La moyenne de ces notes (AVG) s'affiche correctement dans les résultats.

Je souhaite savoir combien de critiques sont prises en compte par AVG, pour chaque film, mais le COUNT me renvoie un résultat 4 fois plus grand qu'espéré. Étrange, car AVG affiche le bon résultat.

Est-ce à cause des JOIN ? Du WHERE ? Faut-il tourner la requête autrement ?

SELECT n.id_film,f.nom_film ROUND(AVG(n.note_scenario),3) AS avg_note, COUNT(*) AS nb_note 
		FROM notes n 
		INNER JOIN films f ON n.id_film = f.id_film 
		INNER JOIN critiques c ON c.id_film = f.id_film 
		WHERE n.note_scenario >= 0 AND c.statut_critique = 3 
		GROUP BY n.id_film,f.nom_film
		ORDER BY avg_note DESC, f.nom_film ASC

Je veux deux tableaux (un pour note_scenario, un pour note_mise_en_scene), comme ça :

note_scenario :

Id	Titre Film	Moyenne Scenario	Nombre de notes composant cette moyenne
1	Inception	6	2
2	The Dark Knight	4.5	2

Voici mes tables :

Films

id_film	nom_film	description_film	statut_film
1	The Dark Knight	Un mystérieux Joker sème le trouble à Gotham City.	1
2	Inception	Un groupe de personnes découvrent des secrets en infiltrant les rêves de puissantes personnes.	1
3	Tron Legacy	Un jeune homme retrouve son père coincé dans un monde virtuel informatique.	0

Critques

id_critique	id_film	id_membre	contenu	statut_critique
1	1	1	J'ai bien aimé ce film, surtout quand Batman s'envole. Le scénario est convainquant par contre la mise en scène vaut celle des Teletubies.	3
2	2	1	Inception se démarque des autres films par la présence exceptionnelle de Leonardo Di Caprio. Scénario pourri mais mise en scène excellente !	3
3	1	2	Batman n'est pas crédible, Ledger en tant que Joker joue très mal. Le scénario craint et la mise en scène est aussi mauvaise que les plats de ma femme.	3
4	2	2	Inception c'est de la fiction pré-mâchée pour ados pré-pubères. Scénario excellentissime mais mise en scène de chien aveugle débutant ayant la rage.	3

Notes

id_note	id_membre	id_film	note_scenario	note_mise_en_scene
1	1	1	7	2
2	2	1	2	1
3	1	2	2	10
4	2	2	10	1

C'est une version simplifiée : il y a parfois 6, parfois 7 critiques par film, et il y a beaucoup de films différents.

Les tables notes et critiques auraient pu être fusionnées, mais je préfère que ça ne le soit pas.



Merci d'avance !

Si je dis pas de bêtises, Count(*) compte le nombre de résultats pour toutes tes colonnes. Tu as 4 colonnes, donc résultat 4 fois plus grand qu'attendu.

Essaye avec un Count(1) qui ne comptera que les résultats de la première colonne.

Merci de la réponse.

J'ai essayé Count(1) et Count(n.note), mais sans succès...

Citation : SixtyFourWarrior

Si je dis pas de bêtises, Count(*) compte le nombre de résultats pour toutes tes colonnes. Tu as 4 colonnes, donc résultat 4 fois plus grand qu'attendu.

Essaye avec un Count(1) qui ne comptera que les résultats de la première colonne.

C'est une bêtise. La clause COUNT compte le nombre de lignes, et non le nombre de colonnes. T'imagines pas le bordel s'il fallait en plus diviser par le nombre de colonnes qui n'est pas nécessairement constant ?

Ici, c'est normal que ça ne fonctionne pas. Que se passe-t-il quand tu crées une jointure ? Une nouvelle table temporaire est générée, si tu connais un peu les probabilités en maths, on pourrait dire que la nouvelle table générée est l'union des tables jointes. Résultat dans cette table tu peux avoir au final d'autres lignes en plus que les notes voulues (et certains champs null). Et donc actuellement tu fais un COUNT sur cette table, d'où le résultat erroné (qui ne l'est pas, SQL fait bien ce que tu lui demandes de faire...).

Il faut préciser quel champ exactement tu veux compter.

Je cherche le nombre de lignes, justement, mais qui sont réunies par le GROUP BY.

Elle veut dire que si il y a au moins un champ non nul dans la tuple, alors COUNT(*) la comptera. Alors que COUNT(foo) comptera uniquement les tuples ayant le champ `foo` non nul

On peut avoir un jeu d'exemple pour mieux comprendre tes tables stp ?

J'ai rajouté plusieurs informations dans mon premier message tout en haut. Si vous pouviez me venir en aide...

Bonjour,

Une proposition de requête

SELECT
  Films.id_film,
  Films.nom_film,
  moyennes.avg_scenario,
  moyennes.avg_mise_en_scene,
  moyennes.nb_Notes,
FROM Films
LEFT OUTER JOIN
  (
    SELECT id_film, AVG(note_scenario) as avg_scenario, AVG(note_mise_en_scene) as avg_mise_en_scene, Count(*) as nb_Notes
    FROM Notes
	LEFT JOIN Critiques ON Critiques.id_critique = notes.id_note
    WHERE Notes.note_scenario>=0 and Notes.note_mise_en_scene>=0 AND Critiques.statut_critique = 3
    GROUP BY (id_film)
  ) as moyennes
  ON moyennes.id_film = Films.id_film
ORDER BY moyennes.avg_scenario DESC, Films.nom_film ASC

L'idée, c'est de faire la jointure sur une table qui contient déjà les moyennes plutôt que de calculer les moyennes après coup.

Normalement, le COUNT(*) renvoit le nombre de lignes associé au GROUP BY. Et ça doit bien fonctionner dans ta requête. C'est la jointure qui pose problème.

Tu fais ta jointure dans le note > film > critique
Tu as bien un seul film lié à chaque note, donc de ce côté là pas de soucis, mais quand tu rajoute les critiques, tu multiplie chaque note par le nombre de critiques associées au film.

En fait, tu as l'impression qu'il te renvoit le nombre total de vote, alors qu'il te renvoie pour chaque film le nombre de vote multiplié par le nombre de critique. Pas de chance, ça donne aussi 4...
Ta jointure sur les critiques aurait donc plutôt dû être "c.id_critique = n.id_note" je suppose

Ça fonctionne, merci beaucoup !

(2 erreurs que j'ai corrigées : la virgule avant le premier FROM, le "as moyennes" avant le dernier ON qui devrait juste être "moyennes")

J'ai fait quelques autres modifications sur ma table "notes" pour que ce soit plus facile, mais l'important est que ça fonctionne. L'idée est bien trouvée, merci !

Les choses vite fait, c'est pas toujours bien fait
Merci pour les corrections.

Si je peux me permettre un commentaire moyennement constructif :

Evite toute orthographe pouvant semer le trouble, càd majuscule et pluriel, que ce soit pour les noms de tables ou leur champ