Re: Problem with group by command

On Fri, 27 Oct 2000, Jessica Ord wrote:

> I would be grateful if any of you could provide me any information that you
> may have.
>
> 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. I used the copy command to migrate most of
> the tables on the new machine and re-run the same program.

You'll probably be best off getting the 7.0.x rpms and using those instead
of 6.5.x (see below).

> The same error message keeps on appear said that:
> Invalid use of aggregates or non-group column in target list.
>
> The SQL statement used is:
> 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;

Well, I don't believe that's a legal SQL statement. All columns in the
select list of a group by query must either be grouped or inside a set
value function (min, max, etc...). I think old versions of postgres got
this wrong, so you may be seeing fallout from that.
Fundamentally, *which* value of date and status would you want to give
if there was more than one for a given username? If there's only one,
using group by is useless, if there's more than one, the query gives
indeterminate results.

Under 7.0.x you can use distinct on and order by to give repeatable
results (if you order by all the fields, and use distinct on (username)
I believe you'd get the first/last row by the ordering criteria that
matched each username)