Re: Problem with group by command

"Jessica Ord" <jo@uk.xo.com> writes:
> I have written a perl program which runs fine on a machine that was using
> PostgreSQL 6.4.2.  I setup another machine which was running PostgreSQL
> 6.5.3 bundled by the Linux 6.2.

> The same error message keeps on appear said that:
> Invalid use of aggregates or non-group column in target list.

> select username, user_index, date, status from subscribers, cellnet_mesg_log
> where status != 3 and date >= '25/10/2000' and date < '01/11/2000' and
> cellnet_mesg_log.user_index = subscribers.oid group by username;

This statement is not valid SQL, but 6.4.* failed to detect that, and
instead gave you some randomly-chosen result.  6.5 is pickier.

You need to revise the query to conform to SQL specs: the SELECT output
list can't refer to columns that aren't grouped by GROUP BY, except as
arguments of an aggregate function like MIN() or MAX().  The reason is
that GROUP BY implies that only one output tuple will be produced per
distinct value of the group-by column(s).  There might be more than one
input tuple, with different values of user_index, date, etc, for each
value of username --- in which case, what output do you expect to get?
It's not well-defined.

            regards, tom lane