Re: SQL: discussion of GROUP BY clause

From: Sybrand Bakker <postbus_at_sybrandb.demon.nl>
Date: Tue, 13 Nov 2001 07:05:49 +0100
Message-ID: <tv1e2v6tk8e315@corp.supernews.com>

"Neil Zanella" <nzanella_at_cs.mun.ca> wrote in message news:b68d2f19.0111121632.5a3d765b_at_posting.google.com...
> 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

IMO you simply just fail to see what GROUP BY is all about. It's _purpose_ is to compute sums, mins, maxs, counts, averages etc _NOT_ to accomplish what could have been done by a simple _ORDER BY_ because that's what you are confusing the GROUP BY with. Also you should keep in mind GROUP BY is an example why SQL should be criticised from the relational perspective. The content of a table is a set. A set is by design unordered, and any elements are purposively unique, as a set can't have nonunique elements. So, from the relational perspective, there is no such thing as a primary key, as it is unneeded. You are trying to add more 'intelligence' than can be allowed in the relational model.

Regards

--
Sybrand Bakker
Senior Oracle DBA

to reply remove '-verwijderdit' from my e-mail address

Received on Tue Nov 13 2001 - 00:05:49 CST