Hi group,
First of all I must express my compliments for SA; it gives me a way to
*play* with databases again. I ran into something small though, hence my
post to the list.
What I notice when using union() is that it removes my original group_by
clauses from the 2 selects that i'm trying to union, and that is illegal if
one of the columns is a function. Consider the following table and selects:
select * from users;
id | name | passwd
----+--------+--------
13 | user1 |
14 | user2 |
16 | user4 |
17 | user6 |
18 | user9 |
19 | user5 | *
20 | user7 | *
21 | user8 | *
22 | user3 | *
23 | user10 | *
(10 rows)
SELECT name, count(id) as nullpasswd, 0 as notnullpasswd
FROM users
WHERE passwd IS NULL
GROUP BY name
UNION
SELECT name, 0 as nullpasswd, count(id) as notnullpasswd
FROM users
WHERE passwd IS NOT NULL
GROUP BY name
name | nullpasswd | notnullpasswd
--------+------------+---------------
user1 | 1 | 0
user10 | 0 | 1
user2 | 1 | 0
user3 | 0 | 1
user4 | 1 | 0
user5 | 0 | 1
user6 | 1 | 0
user7 | 0 | 1
user8 | 0 | 1
user9 | 1 | 0
(10 rows)
which could be useful for the following sql:
SELECT sum(nullpasswd), sum(notnullpasswd)
FROM ( <insert union> )
AS stats;
There might be more ways to get the same output, but having group_by clauses
in the select statements of a union should be possible.
The original group_by clauses seem to be removed (even from the original
select objects) in the class CompoundSelect, line 1338 (SA 0.2.4). but I'm
in doubt on how to fix this in a proper way without creating new bugs.
Thanks,
Kai
--
begin 600 .signature
-------------------------------------------------------------------------
Using Tomcat but need to do more? Need to support web services, security?
Get stuff done quickly with pre-integrated technology to make your job easier
Download IBM WebSphere Application Server v.1.0.1 based on Apache Geronimo
http://sel.as-us.falkag.net/sel?cmd=lnk&kid=120709&bid=263057&dat=121642
_______________________________________________
Sqlalchemy-users mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/sqlalchemy-users
