Thread: group by error message?
Hi, Running this query: critik=# select distinct pt.type_fr,sum(e2.id_event) from person_type pt natural join person_to_event join event e using(id_event) LEFT JOIN event e2 ON e.id_event = e2.id_event AND e2.id_event=219 join event_type et ON e.id_event_type= et.id_event_type where et.type_fr='théâtre' GROUP BY pt.type_fr IS NULL; gives this error: ERROR: column "pt.type_fr" must appear in the GROUP BY clause or be used in an aggregate function But pt.type_fr is _already_ in the group by. When adding e2.id_event however ("GROUP BY pt.type_fr,e2.id_event") it works. Did I miss something there?
Louis-David Mitterrand <vindex+lists-pgsql-general@apartia.org> writes: > critik=# select distinct pt.type_fr,sum(e2.id_event) from person_type pt natural join person_to_event join event eusing (id_event) LEFT JOIN event e2 ON e.id_event = e2.id_event AND e2.id_event=219 join event_type et ON e.id_event_type= et.id_event_type where et.type_fr='th��tre' GROUP BY pt.type_fr IS NULL; > gives this error: > ERROR: column "pt.type_fr" must appear in the GROUP BY clause or be used in an aggregate function > But pt.type_fr is _already_ in the group by. No, "pt.type_fr IS NULL" is in the group by. That doesn't ensure that each group has a unique value of pt.type_fr itself. regards, tom lane
On Thu, Sep 25, 2008 at 11:01:08AM -0400, Tom Lane wrote: > Louis-David Mitterrand <vindex+lists-pgsql-general@apartia.org> writes: > > critik=# select distinct pt.type_fr,sum(e2.id_event) from person_type pt natural join person_to_event join evente using (id_event) LEFT JOIN event e2 ON e.id_event = e2.id_event AND e2.id_event=219 join event_type et ON e.id_event_type= et.id_event_type where et.type_fr='théâtre' GROUP BY pt.type_fr IS NULL; > > > gives this error: > > > ERROR: column "pt.type_fr" must appear in the GROUP BY clause or be used in an aggregate function > > > But pt.type_fr is _already_ in the group by. > > No, "pt.type_fr IS NULL" is in the group by. That doesn't ensure that > each group has a unique value of pt.type_fr itself. Oops, once more I jumped the gun a bit too fast. Thanks for catching it Tom! Best regards,