[Zope-DB] ZSQL

[Dieter Fischer]

Hello

This is a SQL problem. So it depends what kind of DB you're using. In Oracle
you could do something like this (copied from orafaq):

SELECT  *
FROM  (
	SELECT	job,
			sum(decode(deptno,10,sal)) DEPT10,
			sum(decode(deptno,20,sal)) DEPT20,
			sum(decode(deptno,30,sal)) DEPT30,
			sum(decode(deptno,40,sal)) DEPT40
	FROM		scott.emp
	GROUP BY 	job
	)
ORDER BY 1;

you can change it to your needs. But this doesn't help, when you have a
variabe amount of rows (in your case transX). In this case you have to write
something in PL/SQL (Oracle), plpgsql (PostgreSQL) or whatever your DB is
programmed with, or write a Python Script in Zope (not fast, but portable).

Regards

Dieter

> -----Original Message-----
> From: zope-db-bounces at zope.org
> [mailto:zope-db-bounces at zope.org]On Behalf Of Israel Carr
> Sent: Monday, April 17, 2006 10:17 PM
> To: zope-db at zope.org
> Subject: [Zope-DB] ZSQL
>
>
> I have a table with the columns:
> id, username, trans_type
>
> Sample records:
> 1,user1,trans1
> 2,user1,trans3
> 3,user2,trans4
> 4,user2,trans2
> 5,user1,trans1
> 6,user2,trans3
>
>
> I would like to generate output that contains the username and the
> total(count) number of each trans_type.  Would also like to have a total
> of all trans_type.
> Roughly:
>         trans1  trans2  trans3  trans4
> user1     2        0       1       0
> user2     0        1       1       1
>    TOTAL  2        1       2       1
>
>
> I have a ZSQL method with the following:
> select COUNT(*)
> from table
> where trans_type = <dtml-sqlvar transtype type=string>
> 	and username = <dtml-sqlvar username type=string>
>
> When testing and feeding parameters it does give me the count for one
> user and one transaction type.
>
>
> How can I create the desired output and have a general enough ZSQL
> Method that I can feed these parameters?  I've been playing with some
> <dtml-in> tags and trying to call the ZSQL method but am not really sure
> how to pass parameters or if I'm way out in left field.  Would I need to
> call the ZSQL method 8+ times to generate output like the table above?
>
> I can do pretty basic form inputs into database and selects out based on
> form or static values but this is a little more complex for me.  Any
> help would be appreciated.
>
> Israel
>
> _______________________________________________
> Zope-DB mailing list
> Zope-DB at zope.org
> http://mail.zope.org/mailman/listinfo/zope-db
> --
> No virus found in this incoming message.
> Checked by AVG Free Edition.
> Version: 7.1.385 / Virus Database: 268.4.2/314 - Release Date: 16.04.2006
>
--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.385 / Virus Database: 268.4.2/314 - Release Date: 16.04.2006