Re: SQL: discussion of GROUP BY clause

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. Received on Mon Nov 12 2001 - 01:49:48 CST