Re: field must appear in the GROUP BY clause or be used

Hey guys

I have dealt with this before.

And there is a simple solution:  If the value really is unique, just wrap it
in a max().  Since it's unique, it has *ZERO* effect on your output, but it
then complies to PostgreSQL's GROUP BY implementation, and hence will run...



Terry Fielder
Manager Software Development and Deployment
Great Gulf Homes / Ashton Woods Homes
terry@greatgulfhomes.com
Fax: (416) 441-9085


> -----Original Message-----
> From: pgsql-general-owner@postgresql.org
> [mailto:pgsql-general-owner@postgresql.org]On Behalf Of Tom Lane
> Sent: Friday, February 27, 2004 1:09 PM
> To: Mike Mascari
> Cc: Bill Moran; pgsql-general@postgresql.org
> Subject: Re: [GENERAL] field must appear in the GROUP BY clause or be
> used
>
>
> Mike Mascari <mascarm@mascari.com> writes:
> > Bill Moran wrote:
> >> SELECT     GCP.id,
> >> GCP.Name
> >> FROM    Gov_Capital_Project GCP,
> >> WHERE TLM.TLI_ID = $2
> >> group by GCP.id
> >> ORDER BY gcp.name;
> >>
> >> ERROR:  column "gcp.name" must appear in the GROUP BY
> clause or be used
> >> in an aggregate function
> >>
> >> This isn't my query, I'm translating a system prototyped
> in MSSQL to
> >> Postgres.  This query _does_ work in MSSQL.  Does that constitute a
> >> bug in MSSQL, or a shortcomming of Postgres, or just a
> difference of
> >> interpretation?
>
> > If MSSQL picks an arbitrary value for the non-group by
> attribute, it
> > is violating spec.
>
> They might be operating per spec.  If "id" is a primary or unique key
> for the table, then SQL99 (but not SQL92) says that it's sufficient to
> group by the id column; the database is supposed to realize that the
> other columns can't have more than one value per group, and
> allow direct
> references to them.  Or at least that's my interpretation of the pages
> and pages in SQL99 about functional dependency.  It seems
> like a pretty
> useless frammish ... if you know that id is unique, why are you
> bothering with GROUP BY at all?
>
> Anyway, Postgres currently implements the SQL92 definition, which is
> that you can't refer to an ungrouped column except within an aggregate
> function call.  So you need to call out all the columns to be
> referenced
> in GROUP BY.
>
>             regards, tom lane
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>