Skip site navigation (1) Skip section navigation (2)

Re: Problem with group by command

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Jessica Ord" <jo(at)uk(dot)xo(dot)com>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: Problem with group by command
Date: 2000-10-28 00:50:37
Message-ID: 6058.972694237@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-bugs
"Jessica Ord" <jo(at)uk(dot)xo(dot)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

In response to

pgsql-bugs by date

Next:From: Stephan SzaboDate: 2000-10-28 04:21:07
Subject: Re: MySQL/PostgreSQL discrepancy
Previous:From: Stephan SzaboDate: 2000-10-27 21:17:05
Subject: Re: Problem with group by command

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group