Re: Why using "Group By"

From: Bob Badour <>
Date: Thu, 13 Mar 2003 16:13:06 -0500
Message-ID: <kZ6ca.74$>

"Mikito Harakiri" <> wrote in message news:F86ca.16$
> "Bob Badour" <> wrote in message
> news:pS5ca.65$
> > If you have access to sybase (or sqlserver probably), try a query like:
> >
> > select a, b, count(*) from sometable group by a
> >
> > They seem to treat the expression as some sort of request for a cross
> > product of sometable with itself just to tie up resources on the server.
> If
> > the statement gave an error instead, one could at least point out that
> > redundancy aids compile-time error detection.
> This result might be consistent with C.J.Date proposal to treat
> select a, b, count(*) from sometable group by a
> as
> select distinct a, b,
> (select count(*) from sometable as it where it.a = ot.a)
> from sometable as ot

I am unfamiliar with the proposal you mention, but that is not the result I recall getting in any case.

> For syntactically correct "group by" (when the select column list is a
> subset of "group by" list) rewritten the results are identical.

Except that the only purpose of this is to destroy logical identity. Is this as good as maintaining logical identity? I think not.

> Your case
> can be considered as a "generalized group by". This kind of output is
> routinely produced by "Window" aggregate functions. Are window analytical
> extensions legitimate from theoretical point of view?
> BTW, oracle rejects "group by" query where select list is inconsistent
> column list in the "group by" clause.

Good. Errors should be errors. I have never seen anyone write a query with an inconsistent group by that was not an error. Since a relation with distinct a & b values extended with a count of distinct a's already has several redundant syntactically correct formulations, I think we can safely reject the inconsistent group by as an error.

> Anybody tested DB2?

Not me. Received on Thu Mar 13 2003 - 22:13:06 CET

Original text of this message