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

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

From: Bill Moran <wmoran(at)potentialtech(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: field must appear in the GROUP BY clause or be used in an aggregate function?
Date: 2004-02-27 16:11:28
Message-ID: 403F6C30.1060304@potentialtech.com (view raw or flat)
Thread:
Lists: pgsql-general
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?

-- 
Bill Moran
Potential Technologies
http://www.potentialtech.com


Responses

pgsql-general by date

Next:From: Ron St-PierreDate: 2004-02-27 16:11:31
Subject: Re: change db encoding
Previous:From: Mike MascariDate: 2004-02-27 16:02:36
Subject: Re: correlated delete with 'in' and 'left outer join'

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