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

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

From: Richard Huxton <dev(at)archonet(dot)com>
To: Bill Moran <wmoran(at)potentialtech(dot)com>, johnsw(at)wardbrook(dot)com
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: field must appear in the GROUP BY clause or be used
Date: 2004-02-27 17:00:27
Message-ID: 200402271700.28133.dev@archonet.com (view raw or flat)
Thread:
Lists: pgsql-general
On Friday 27 February 2004 16:39, Bill Moran wrote:
> John Sidney-Woollett wrote:
> > Bill Moran said:
> >>
> >>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

> 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

Like the error message says, if you're using GROUP BY everything in the SELECT 
list must be an aggregate SUM(...) or used in the GROUP BY.

So, this is OK:
  SELECT dept, week, SUM(amt_sold)
  FROM weekly_sales
  GROUP BY dept,week;
This isn't:
  SELECT dept, week, SUM(amt_sold)
  FROM weekly_sales
  GROUP BY dept;

Ask yourself which "week" should be returned in the second case.

-- 
  Richard Huxton
  Archonet Ltd

In response to

pgsql-general by date

Next:From: Michael ChaneyDate: 2004-02-27 17:01:10
Subject: Re: correlated delete with 'in' and 'left outer join'
Previous:From: Mike MascariDate: 2004-02-27 16:53:13
Subject: Re: field must appear in the GROUP BY clause or be used

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