On Wed, 14 Jul 1999, Thomas Lockhart wrote:
> > Unless anyone can come up with a better way to do this, What is the best
> > way to implement a conversion from bool to int?
>
> select sum(case when bfield = TRUE then 1 else 0 end) from table;
I'm not sure this is correct, but I think I see a bug of some sort...
SELECT folderid,foldername,count(*),sum(contentlength),sum(case when
flagnew = TRUE then 1 else 0 end) FROM usermail,folders WHERE
usermail.loginid='michael' and folders.loginid=usermail.loginid AND
usermail.folder = folders.folderid GROUP BY folderid,foldername UNION
SELECT folderid,foldername,0,0,0 FROM folders WHERE loginid='michael' AND
NOT EXISTS (SELECT folder FROM usermail WHERE loginid='michael' AND
folder=folderid) ;
ERROR: _finalize_primnode: can't handle node 723
It seems to be the union that is confuzing it...
SELECT folderid,foldername,count(*),sum(contentlength),sum(case when
flagnew = TRUE then 1 else 0 end) FROM usermail,folders WHERE
usermail.loginid='michael' and folders.loginid=usermail.loginid AND
usermail.folder = folders.folderid GROUP BY folderid,foldername;
folderid|foldername |count| sum|sum
--------+----------------+-----+-------+--- -4|Deleted Messages| 110| 245627| 50 -2|Sent Mail | 7|
10878| 2 -1|New Mail Folder | 73|8831226| 1 1|OOL | 7| 8470| 0
etc
-Michael