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 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Michael Chaney | 2004-02-27 17:01:10 | Re: correlated delete with 'in' and 'left outer join' |
Previous Message | Mike Mascari | 2004-02-27 16:53:13 | Re: field must appear in the GROUP BY clause or be used |