Re: SQL: discussion of GROUP BY clause

When I don't want some of the GROUP BY expressions in my result set, I usually write the SELECT..GROUP BY as a dynamic table. For example

select sum_b
from (select a, sum(b) sum_b from t group by a)

"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.
Received on Mon Nov 12 2001 - 09:01:54 CST