Re: SQL: discussion of GROUP BY clause

From: Nicholas Carey <ncarey_at_speakeasy.org>
Date: Tue, 13 Nov 2001 05:59:59 -0000
Message-ID: <Xns9157DFCB5E7B3ncareyspeakeasyorg@207.126.101.92>

On 12 Nov 2001, nzanella_at_cs.mun.ca (Neil Zanella) spake and said:

> 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.

GROUP BY cares not a whit about keys, primary or other. All GROUP BY does is collapse the set so that one row is returned for every distinct combination of values for the columns specified in the GROUP BY list. Another way of looking at it is to view it as sequencing the set by the group by list, then processing it in sequence. At each sequence break, one row and one row only is emitted. As it processes rows, group by can tote up the various aggregate functions -- count(), sum(), avg(), etc.

But that's it. That's group by's functionality, in toto.

And in that context, the notion of returning something *other* than a constant or an aggregate makes no sense whatsoever.-- it's undefined. Taking my previous example -- if you issue this select statement

  select A, B from foo group by A

against a table with two columsn, A and B, consider a group of 1000 rows: each row has a common value for A, and each row has a unique, distinct value for B. What on earth could this select statement possibly return that would make sense in the context of what GROUP BY is designed to do?

Micro$oft (and Sybase's) SQL Server used to allow [emphasis on the 'used to' part -- v4.x and earlier] allow non-constant, nonaggregating  and non-grouping stuff in a select statements containing group by. Something along the lines of what you are talking about:

  select A, B, count(*) from foo group by A

Which didn't do grouping, but instead performed an [undocumented] equivalent of a join (SQL-92 syntax):

  select t1.A ,
         t1.B ,
         t2.

  from foo t1
  join ( select A , count(*) cnt

         from foo
         group by A
       ) t2 on t2.A = t2.A

And if that's what you want, why not just ask for it? This behaviour was Bad because the semantics of GROUP BY changed (morphed) depending on what you put in the result list and in the group by list. A minor error in an expression and you got something completely unrelated to what you were looking for. Received on Mon Nov 12 2001 - 23:59:59 CST