Mailing List Archive

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?

--
Federico Di Gregorio
MIXAD LIVE Chief of Research & Technology fog@mixadlive.com
Debian GNU/Linux Developer & Italian Press Contact fog@debian.org
Lord, defend me from my friends; I can account for my enemies.
-- Charles D'Hericault

> 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?

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
>

This is a suggestion and a question as I'm a relative Zope Newbie and an SQL
Newbie while I'm at it...

Why not have zope look at the figures returned in the column, and just add
'em up and then divide by the number of times it had a number to go through?
Each row.column is an object, right? Is it possible to get the object's
value, and then add it, like a "for" loop, but in DTML?

Hope this is helpful, and if not, I hope I get a good answer myself!

Russell

On Monday 03 September 2001 03:43, Mitch Pirtle wrote:
> > 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

Hi Zopistas,

The winning answer was from Pavel V. Piankov
[pashah@cosmos-mebel.spb.ru] who had the following angle on life:

<dtml-call "REQUEST.set('total',0)">
<p>
<dtml-in "zsql.dbGetSalary(_,_.None)">
Name: <dtml-var name> - <dtml-var salary> salary<br>
<dtml-call "REQUEST.set('total',total+_.float(salary))">
</dtml-in>
Total: <dtml-var total>
</p>

..and in the process taught me a little about keeping variables around
in DTML -;^>= Thanks to Pavel for the help, I have agonized over this
for a humiliatingly unacceptable amount of time...

(forehead slap)


> -----Original Message-----
> From: Russell Hires [mailto:rhires@earthlink.net]
> Sent: Monday, September 03, 2001 15:27
> To: Mitch Pirtle
> Cc: zope-db@zope.org
> Subject: Re: [Zope-DB] ZSQL, sum() and you
>
>
> This is a suggestion and a question as I'm a relative Zope
> Newbie and an SQL
> Newbie while I'm at it...
>
> Why not have zope look at the figures returned in the column,
> and just add
> 'em up and then divide by the number of times it had a number
> to go through?
> Each row.column is an object, right? Is it possible to get
> the object's
> value, and then add it, like a "for" loop, but in DTML?
>
> Hope this is helpful, and if not, I hope I get a good answer myself!
>
> Russell
>
> On Monday 03 September 2001 03:43, Mitch Pirtle wrote:
> > > 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
>

Mitch Pirtle writes:
> 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?
You should look at "dtml-in"s statistics variables, e.g.
at

<http://www.dieter.handshake.de/pyprojects/zope/book/chap3.html>



Dieter

Mitch Pirtle wrote:
> Hi Zopistas,
>
> The winning answer was from Pavel V. Piankov
> [pashah@cosmos-mebel.spb.ru] who had the following angle on life:
>
> <dtml-call "REQUEST.set('total',0)">
> <p>
> <dtml-in "zsql.dbGetSalary(_,_.None)">
> Name: <dtml-var name> - <dtml-var salary> salary<br>
> <dtml-call "REQUEST.set('total',total+_.float(salary))">
> </dtml-in>
> Total: <dtml-var total>
> </p>
>
> ..and in the process taught me a little about keeping variables around
> in DTML -;^>= Thanks to Pavel for the help, I have agonized over this
> for a humiliatingly unacceptable amount of time...
>
> (forehead slap)
>

Well, you could do it that way, but the dtml-in tag has a lot of power
and is actually ready for you to do exactly what you want...

<p>
<dtml-in "zsql.dbGetSalary(_,_.None)">
Name: <dtml-var name> - <dtml-var salary> salary<br>
<dtml-if sequence-end>
Total: <dtml-var total-salary>
</dtml-if>
</dtml-in>
</p>


--
Tom Jenkins
devIS - Development Infostructure
http://www.devis.com