[Zope] Using SQL aggregate functions

[Tres Seaver]

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