counting bools in a complex query
От | Michael Richards |
---|---|
Тема | counting bools in a complex query |
Дата | |
Msg-id | Pine.BSF.4.10.9907132029580.38362-100000@scifair.acadiau.ca обсуждение исходный текст |
Ответы |
Re: [SQL] counting bools in a complex query
(Herouth Maoz <herouth@oumail.openu.ac.il>)
|
Список | pgsql-sql |
Hi, I think I've created a monster... Working on an email system I have the following: Table = usermail +----------------------------------+--------------------------+-------+ | Field | Type | Length| +----------------------------------+--------------------------+-------+ | contentlength | int4 | 4 | | folder | int4 | 4 | | flagnew | bool | 1 | etc... And: Table = folders +----------------------------------+--------------------------+-------+ | Field | Type | Length| +----------------------------------+--------------------------+-------+ | loginid | varchar() not null | 16 | | folderid | int4 not null default ( | 4 | | foldername | varchar() | 25 | etc... So each email message has an entry in usermail, and each mail folder has an entry in folders. I need to extract the following info: foldername, number of messages in that folder, number of messages in that folder with flagread set, total size of all the messages in each folder Since postgres does not appear to support outer joins, I've come up with a really icky query that almost does what I want: SELECT folderid,foldername,count(*),sum(contentlength) 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,null,null FROM folders WHERE loginid='michael' AND folderid NOT IN (SELECTfolder FROM usermail WHERE loginid='michael'); WHEW! folderid|foldername |count| sum --------+----------------+-----+------- -4|Deleted Messages| 110| 245627 -3|Saved Drafts | | -2|SentMail | 7| 10878 -1|New Mail Folder | 73|8831226 1|OOL | 7| 8470 etc... My final problem is to count all the messages with flagnew set to true. The only way I can think to do this is to convert the bool value to a 1 or 0 (which I think should be a standard conversion anyway) and run a sum() on them. 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? -Michael
В списке pgsql-sql по дате отправления: