[Zope-DB] ZSQL, sum() and you

[Matthew T. Kromer]

With MySQL, you can do a SUM if you have a GROUP BY clause; e.g.

select *, sum(salary) as total_salary from emp group by empid

but that's not going to give you quite what you want -- since the group by
clause will break your sum into subsets. Now, if there's the equivalent of
DUAL in the database, you might be able to do a join with it, grouping on
it... ie a join with a 1-row 1-column table, grouping on that column.  I
didn't try with MySQL, largely because I'm being lazy.


----- Original Message -----
From: "Mitch Pirtle" <mitchy@spacemonkeylabs.com>
To: "'Federico Di Gregorio'" <fog@mixadlive.com>
Cc: <zope-db@zope.org>
Sent: Monday, September 03, 2001 3:43 AM
Subject: RE: [Zope-DB] ZSQL, sum() and you


> > Scavenging the mail folder uncovered Mitch Pirtle's letter:
> > > Imagine, if you will, a ZSQL method called dbGetEmp
> > containing "select
> > > * from emp"; and want to also retrieve sum(salary).  This is with
> > > tables in MySQL.  For the life of me, I cannot find any summing
> > > features in Zope - do I gotta do this in Python just to get
> > a total on
> > > one column?
> >
> > maybe i don't understand correctly the problem, but wouldn't...
> >
> >       SELECT *, sum(salary) as sum_of_salary FROM emp
> >
> > do it?
>
> Not in MySQL, and I believe/think/ASSume that's illegal SQL.  (cannot
> include GROUP statements within a non-GROUP query or something of the
> sort).
>
> Basically, I'm returning all rows from a table, and then wanting a sum
> from one column.  In PHP, you run the query, display (via loop) the
> rows, then calculate the sum of one column (from the same recordset,
> without re-running the query).  I cannot figure out the equivalent in
> DTML, and am not excited to use python just to get a total.
>
> I know how to do this in PL/SQL, but no Oracle here - is there a ZSQL
> equivalent?
>
> Anybody else out there?  Bueller?
>
>
> _______________________________________________
> Zope-DB mailing list
> Zope-DB@zope.org
> http://lists.zope.org/mailman/listinfo/zope-db
>