John Williams wrote:
"Tres" == Tres Seaver <tseaver@palladion.com> writes:
Tres> John Williams <J.A.R.Williams@aston.ac.uk> asked: >> Date: 28 Feb 2000 08:17:42 +0000 >> >> I have been trying to use SQL aggregate functions in SQL >> Methods to calculate some statistics e.g. something like >> >> SELECT id,count(id) from data group by field >> >> I am using MySQL, and the query works if entered directly from >> another client, however Zopes SQL method returns an error. Is >> this a known problem. Is there a known workaround or patch?
Tres> I believe that the aggregate column has a "name" which the Tres> DA doesn't like. Try aliasing it (the syntax for this Tres> depends on your RDBMS)::
Tres> SELECT id,count(id) as count_id from data group by field
I was in fact using an alias, and this isn't cure the problem. I have since looked in a bit more detail and the problem appears to occur only when I use a join in the query, so it is a bit more subtle than I originally thought.
I saw another post with a patch for mySQL to cope with a column whose type was coming back "unhandled". Perhaps it would shed some light? Also, the SQL you posted looks a little odd: the non-aggregate column 'id' is not one of the 'group by' columns, which will cause Oracle at least to barf on the statement. -- ========================================================= Tres Seaver tseaver@palladion.com 713-523-6582 Palladion Software http://www.palladion.com