CODEWITHSUNDEEP
sqlmysql
Jimmy
Jimmy

Reputation: 978

MySQL AVG() value not resetting on every row

I have this very weird results when trying to fetch the AVG() field from my "reponse" field.

Query : 

 SELECT AVG(Reponse.note) as noteMoyenne, Categorie.titre,
      Autorisation.typeEvaluateur, COUNT(DISTINCT Autorisation.id) as nbEvaluateur
 FROM reponses as Reponse, categories as Categorie, questions as Question,
      autorisations as Autorisation
 WHERE Reponse.question_id = Question.id AND 
        Question.categorie_id = Categorie.id AND 
        Reponse.note != 0 AND
        Reponse.evaluation_id = $id AND
        Autorisation.evaluation_id = $id AND
        Autorisation.complete = 1
 GROUP BY Categorie.titre, Autorisation.typeEvaluateur
 ORDER BY Categorie.id;

I would expect the results to be something like : 

   noteMoyenne      Cat   typeEvaluateur  nbEvaluateur
    4.0225          Cat1    TypeA               3
    **1.6425        Cat1    TypeB               1
    3.4123          Cat2     TypeA               5
    ....

But it seems like the average is kept for the every single categories and then reset when there's a new Category. Actual data returned : 

 noteMoyenne    Cat     typeEvaluateur  nbEvaluateur
   4.0225       Cat1    TypeA               3
   **4.0225     Cat1    TypeB               1
   3.4123       Cat2     TypeA               5
   ....

Any help would be greatly appreciated, I'm kinda lost with this.

Thanks!

Upvotes: 0

Views: 166

Answers (1)

davethegr8
davethegr8

Reputation: 11595

Since you've got two fields in the GROUP BY clause, it calculates the AVG for each of the different GROUP BY pairs. You will likely need to have another query (or subquery) to get what you're looking for.

Upvotes: 2

Related Questions