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

Re: field must appear in the GROUP BY clause or be used in an aggregate function?

From: Michael Chaney <mdchaney(at)michaelchaney(dot)com>
To: Bill Moran <wmoran(at)potentialtech(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: field must appear in the GROUP BY clause or be used in an aggregate function?
Date: 2004-02-27 18:05:06
Message-ID: 20040227180506.GB8807@michaelchaney.com (view raw or flat)
Thread:
Lists: pgsql-general
On Fri, Feb 27, 2004 at 11:11:28AM -0500, Bill Moran wrote:
> Hey all.
> 
> I've hit an SQL problem that I'm a bit mystified by.  I have two different
> questions regarding this problem: why?  and how do I work around it?
> 
> The following query:
> 
> SELECT 	GCP.id,
> 	GCP.Name
>     FROM    Gov_Capital_Project GCP,
>     WHERE TLM.TLI_ID = $2
>     group by GCP.id
>     ORDER BY gcp.name;
> 
> Produces the following error:
> 
> ERROR:  column "gcp.name" must appear in the GROUP BY clause or be used in 
> an aggregate function
> 
> That field is a CHAR, so I'm not sure what kind of aggregate to use,
> or (more important to my understanding) why one is necessary.
> 
> As I said, I'm not sure I understand why this occurs.  I'm assuming that I
> don't understand "group by" as well as I thought I did ;)
> 
> 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?

Well, if "non-standard" == "bug", then it's a bug in mssql.  Your query
doesn't make any sense.  What value for "Name" should be chosen if
there's more than one?

Michael
-- 
Michael Darrin Chaney
mdchaney(at)michaelchaney(dot)com
http://www.michaelchaney.com/

In response to

pgsql-general by date

Next:From: Tom LaneDate: 2004-02-27 18:09:12
Subject: Re: field must appear in the GROUP BY clause or be used
Previous:From: Mike MascariDate: 2004-02-27 18:03:02
Subject: Re: correlated delete with 'in' and 'left outer join'

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