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

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