MySQL AVG() value not resetting on every row.

Question

Hello,

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!

Answer 1

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.