Re: [HACKERS] Counting bool flags in a complex query

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