Re: SQL: discussion of GROUP BY clause

Nicholas Carey <ncarey_at_speakeasy.org> wrote in message news:<Xns9156F26955274ncareyspeakeasyorg_at_207.126.101.92>...
> On 10 Nov 2001, Neil Zanella <nzanella_at_garfield.cs.mun.ca>
> spake and said:
>
> >
> > Hello,
> >
> > The SQL standard imposes that when using the GROUP BY clause
> > all attributes appearing in the SELECT clause must also
> > appear in the GROUP BY clause. This design decision has its
> > good reasons for being there but there are cases in which
> > this is a bit of a hinderance.
>
> OK. I'll bite. The SQL standards mandates that all columns in the
> result set of a select statment that has GROUP BY in it are
> either (A) a constant, (B) an element of the GROUP BY list, or
> (C) an aggregate function operating on the group.
>
> You think that this is a problem. Why?
>
> Consider this table:
>
> A B
> -- --
> 1 A
> 1 A
> 2 A
> 2 B
> 2 C
> 3 C
> 3 D
>
> and this piece of trivial SQL:
>
> select a , b
> from foo
> group by a
>
> Assuming that the Standard's requirements for GROUP BY don't hold
> true for this example, what does column B return for any given
> group A? Bear in mind that GROUP BY summarizes, so that a single
> row is returned for each group.

All I am saying is that the standard should be more flexible. In the above example A is not the primary key of the given table thus for each valuein
column A there can be multiple values for the corresponding element in column B as you pointed out. However if A was a primary key then I see no reason why the standard should not accept the above query. Thus the standard should add to (A), (B), and (C) the following: (D) any columns whose value is uniquely determined by those appearing in
the GROUP BY clause for any instance of the given table. In particular, if
the primary key appears in the GROUP BY clause then any column should be
allowed in the SELECT clause.

Regards,

Neil Received on Mon Nov 12 2001 - 18:32:31 CST