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

[Mike Mascari <mascarm(at)mascari(dot)com>]

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:

[test(at)lexus] select * from projects;
   dept    |   project
-----------+--------------
 Finance   | Y2K
 Corporate | Y2K
 Corporate | Annual Audit
(3 rows)


[test(at)lexus] 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'?

[test(at)lexus] 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:

[test(at)lexus] select dept, count(project) from projects group by dept;
   dept    | count
-----------+-------
 Finance   |     1
 Corporate |     2
(2 rows)

or perhaps:

[test(at)lexus] 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