Re: Why using "Group By"

From: Bob Badour <bbadour_at_golden.net>
Date: Thu, 13 Mar 2003 15:02:15 -0500
Message-ID: <TW5ca.66$LO2.8345655@mantis.golden.net>

"Damjan S. VujnoviÄ?" <damjan_at_NO_SPAM.galeb.etf.bg.ac.yu> wrote in message news:b4qlq3$rbo$1_at_news.etf.bg.ac.yu...
> Mikito Harakiri wrote:
> > "Damjan S. Vujnovi??" <damjan_at_NO_SPAM.galeb.etf.bg.ac.yu> wrote in
message
> > news:b4prme$fnl$1_at_news.etf.bg.ac.yu...
> >
> >>How will you write (using your sintax) something like this:
> >>
> >>SELECT
> >> CUSTOMER_CITY, COUNT(*)
> >>FROM
> >> CUSTOMER_TABLE
> >>GROUP BY
> >> CUSTOMER_CITY, SOME_OTHER_CUSTOMER_TABLE_ATTRIBUTE
> >
> >
> > select CUSTOMER_CITY, CNT from (
> > SELECT
> > CUSTOMER_CITY,
> > SOME_OTHER_CUSTOMER_TABLE_ATTRIBUTE,
> > COUNT(*) as CNT
> > FROM
> > CUSTOMER_TABLE
> > -- implicit GBY
> > )
> >
> >

>

> Good point - I found it myself too, but was too late :) Also the example
> in your latest post seems to be quite adequate. The example I found
> later was quite similar:

>

> SELECT MAX(y) AS maxcount
> FROM (SELECT COUNT(*) AS y
> FROM t
> GROUP BY x)

Why is that superior (or even as good as) the following?

select max(y) as maxcount from ( select x, count(*) as y from t)

Even with group by, one has to allow the equivalent query of:

SELECT MAX(y) AS maxcount
FROM (
  SELECT x, COUNT(*) AS y
  FROM t
  GROUP BY x
)

It seems to me that GROUP BY is totally useless for anything but discarding logical identity. Received on Thu Mar 13 2003 - 14:02:15 CST