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

[Mike Mascari]

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
The reason the grouping requires either an attribute to be 
aggregated or apart of the group by list is that if it were not, an 
arbitrary value would have to be selected:

[EMAIL PROTECTED] select * from projects;
   dept    |   project
-----------+--------------
 Finance   | Y2K
 Corporate | Y2K
 Corporate | Annual Audit
(3 rows)
[EMAIL PROTECTED] select dept, project from projects group by dept;
ERROR:  column "projects.project" must appear in the GROUP BY clause 
or be used in an aggregate function

If this were to be permitted, which project should be selected, 
'Y2K' or 'Annual Audit'?

[EMAIL PROTECTED] select dept, project from projects group by dept, project;
   dept    |   project
-----------+--------------
 Corporate | Y2K
 Corporate | Annual Audit
 Finance   | Y2K
(3 rows)
Of course, this has little meaning without an aggregate. All you're 
doing is leveraging GROUP BY's sort. You might as well use DISTINCT. 
More useful would be:

[EMAIL PROTECTED] select dept, count(project) from projects group by dept;
   dept    | count
-----------+-------
 Finance   |     1
 Corporate |     2
(2 rows)
or perhaps:

[EMAIL PROTECTED] select count(dept), project from projects group by project;
 count |   project
-------+--------------
     2 | Y2K
     1 | Annual Audit
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?
If MSSQL picks an arbitrary value for the non-group by attribute, it 
is violating spec.

Mike Mascari



---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
              http://www.postgresql.org/docs/faqs/FAQ.html