Field must appear in the GROUP BY clause or be used in an aggregate function

The following query works well in MySQL:


SELECT COUNT(b.id) AS level, a.*
  FROM saw_user_group a,
       saw_user_group b
 WHERE a.lft BETWEEN b.lft AND b.rgt
   AND a.id = 1
GROUP BY a.id

But fails in postgres with this error:

a.name must appear in the GROUP BY clause or be used in an aggregate function

Any ideas on how I can solve this problem?

Read the error message again. You are doing an aggregate function as well as selecting everything from table a (a.*). It’s telling you that you have to have all the fields that aren’t in the aggregate function in the group by. Since a.name is the next field in the table it has told you that that field is the one causing the error.

To fix, include all the fields from table a in the GROUP BY clause - instead of just a.id


  SELECT COUNT(b.id) AS level, a.id, a.name, a.field3
  FROM saw_user_group a,
  saw_user_group b
  WHERE a.lft BETWEEN b.lft AND b.rgt
  AND a.id = 1
  GROUP BY a.id, a.name, a.field3
  

(a.field3 should be replaced with the names of all the other fields in your saw_user_group table)

great explanation

rule of thumb: you can’t use “select star” with GROUP BY

p.s. fbronx, it doesn’t work “well” in mysql – in fact, they actually warn you in the docs that if you do it, and all the “hidden” columns (those not in the GROUP BY) aren’t 1-to-1 with the GROUP BY columns, then the results are unpredictable

in every other database you just plain can’t do it, so i wouldn’t call what mysql does “doing it well”

:slight_smile: