Re: [GENERAL] group by error message?

2008-09-25 Thread Tom Lane 
Louis-David Mitterrand [EMAIL PROTECTED] writes:
   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.

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

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] group by error message?

2008-09-25 Thread Louis-David Mitterrand 
On Thu, Sep 25, 2008 at 11:01:08AM -0400, Tom Lane wrote:
 Louis-David Mitterrand [EMAIL PROTECTED] writes:
  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.
 
 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,

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general