Thread

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

Michael Richards <miker@scifair.acadiau.ca> — 1999-07-14T16:34:33Z

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