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

John Sidney-Woollett wrote:
> Bill Moran said:
>
>>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
>
> Since you're not agregating data, can't you use a select distinct instead?

Not sure.  I'll have to get back to the programmer who wrote the orignal
SELECT and find out what kind of data he is actually trying to acquire.

> SELECT  distinct GCP.id, GCP.Name
> FROM Gov_Capital_Project GCP, {?something missing here?}
> WHERE TLM.TLI_ID = $2
> ORDER BY gcp.name;
>
> (BTW, I wasn't clear if the where clause trying to join to another table?)

Yes, my bad.  The actual query causing the problem is a bit longer with about
6 joins to it.  I did test:

select id, name from gov_capital_project group by id order by name;

and it causes the same error, so I thought I'd make the question simpler by
removing the parts that obviously weren't contributing to the problem.

> Doesn't answer your original question, but hope it helps anyway.

It may, thanks for the input!

Like I said, the most important part (to me) is to understand why
Postgres refuses to run this.  The fact that I don't know why points
to an obvious lack of understanding on my account, and I'd like to
remedy that :D

To that effect, if anyone can point me to a doc that will help me
gain a better understanding of why this error occurs, I'd be happy
to read it!

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