[thelist] [mysql] count query

[Joshua Olson]

----- Original Message ----- 
From: "rudy" <rudy937 at rogers.com>
Sent: Thursday, September 11, 2003 11:04 AM


> you cannot have the WHERE clause after the GROUP BY
>
> but you knew that   ;o)

Rudy, you got me.

Yep... slip of the cut'n'paste.

> > You *must* group by any columns that are not
> > aggregated in the select clause when you have
> > at least one that is aggregated.
>
> that's usually true, except that the GROUP BY clause
> is optional
>
> if it's missing, the entire record set is considered
> a group, and the result is one row

Rudy, you lost me.  That last statement may be true, but...

Oddly enough, MS SQL Server throws an error if you have a Count() in the
select list without a GROUP BY clause:

Column 'xxx' is invalid in the select list because it is not contained in an
aggregate function and there is no GROUP BY clause.

Sooo... what is the basis for your claim?  What does the standard say
regarding this?  M$ is known to make their own rules, but in this case their
requirement makes perfect sense, logically?  If you had a count on one
column and a couple other random columns in the select list, which detail
would you return with that one record? ??

> further complicating matters is the fact that
> the "must" is not true in mysql!!
>
> mysql has a proprietary extension (if you can call it
> that) that allows you to have non-aggregate columns in
> the SELECT list that are not in the GROUP BY

Yeah, we've discussed that before when I noticed that in theory you should
be able to make that shortcut.  Given that it's a "proprietary extension",
I'll stick to my *must*.

<><><><><><><><><><>
Joshua Olson
Web Application Engineer
WAE Tech Inc.
http://www.waetech.com
706.210.0168