Login | Register For Free | Help
Search for: (Advanced)

Mailing List Archive: Zope: DB

ZSQL, sum() and you

 

 

Zope db RSS feed   Index | Next | Previous | View Threaded


mitchy at spacemonkeylabs

Sep 3, 2001, 12:11 AM

Post #1 of 8 (1512 views)
Permalink
ZSQL, sum() and you

Hi folks,

There's something I've been wanting to ask, but after lurking around
I've noticed I'm the only one with this problem. That occasion usually
precedes an embarassing episode -;^>=

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?

Sure, I can do it with two separate queries, but argh that's just plain
sick.

Apologies in advance for missing the obvious, as I know this is taken
care of somewhere...


fog at mixadlive

Sep 3, 2001, 12:27 AM

Post #2 of 8 (1486 views)
Permalink
Re: ZSQL, sum() and you [In reply to]

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 [at] mixadlive
Debian GNU/Linux Developer & Italian Press Contact fog [at] debian
Lord, defend me from my friends; I can account for my enemies.
-- Charles D'Hericault


mitchy at spacemonkeylabs

Sep 3, 2001, 12:43 AM

Post #3 of 8 (1484 views)
Permalink
RE: ZSQL, sum() and you [In reply to]

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


matt at zope

Sep 3, 2001, 5:15 AM

Post #4 of 8 (1485 views)
Permalink
Re: ZSQL, sum() and you [In reply to]

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 [at] spacemonkeylabs>
To: "'Federico Di Gregorio'" <fog [at] mixadlive>
Cc: <zope-db [at] zope>
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 [at] zope
> http://lists.zope.org/mailman/listinfo/zope-db
>


rhires at earthlink

Sep 3, 2001, 6:27 AM

Post #5 of 8 (1477 views)
Permalink
Re: ZSQL, sum() and you [In reply to]

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 [at] zope
> http://lists.zope.org/mailman/listinfo/zope-db


mitchy at spacemonkeylabs

Sep 3, 2001, 6:33 AM

Post #6 of 8 (1480 views)
Permalink
RE: ZSQL, sum() and you [In reply to]

Hi Zopistas,

The winning answer was from Pavel V. Piankov
[pashah [at] cosmos-mebel] 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 [at] earthlink]
> Sent: Monday, September 03, 2001 15:27
> To: Mitch Pirtle
> Cc: zope-db [at] zope
> 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 [at] zope http://lists.zope.org/mailman/listinfo/zope-db
>


dieter at handshake

Sep 3, 2001, 3:36 PM

Post #7 of 8 (1492 views)
Permalink
Re: ZSQL, sum() and you [In reply to]

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


tjenkins at devis

Sep 4, 2001, 5:38 AM

Post #8 of 8 (1482 views)
Permalink
Re: ZSQL, sum() and you [In reply to]

Mitch Pirtle wrote:
> Hi Zopistas,
>
> The winning answer was from Pavel V. Piankov
> [pashah [at] cosmos-mebel] 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

Zope db RSS feed   Index | Next | Previous | View Threaded
 
 


Interested in having your list archived? Contact Gossamer Threads
 
  Web Applications & Managed Hosting Powered by Gossamer Threads Inc.