pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
counting bools in a complex query
2+ messages / 2 participants
[nested] [flat]

* counting bools in a complex query
@ 1999-07-13 23:31 Michael Richards <miker@scifair.acadiau.ca>
  1999-07-14 09:43 ` Re: [SQL] counting bools in a complex query Herouth Maoz <herouth@oumail.openu.ac.il>
  0 siblings, 1 reply; 2+ messages in thread

From: Michael Richards @ 1999-07-13 23:31 UTC (permalink / raw)
  To: 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 
          (SELECT folder FROM usermail WHERE loginid='michael');

WHEW!

folderid|foldername      |count|    sum
--------+----------------+-----+-------
      -4|Deleted Messages|  110| 245627
      -3|Saved Drafts    |     |       
      -2|Sent Mail       |    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




^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: [SQL] counting bools in a complex query
  1999-07-13 23:31 counting bools in a complex query Michael Richards <miker@scifair.acadiau.ca>
@ 1999-07-14 09:43 ` Herouth Maoz <herouth@oumail.openu.ac.il>
  0 siblings, 0 replies; 2+ messages in thread

From: Herouth Maoz @ 1999-07-14 09:43 UTC (permalink / raw)
  To: Michael Richards <miker@scifair.acadiau.ca>; pgsql-sql

At 02:31 +0300 on 14/07/1999, Michael Richards wrote:


> 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?

Depends on your version. If you have 6.5, you can probably use COALESCE or
CASE. In previous versions, I would define an SQL function. The first one
that comes to mind would be:

CREATE FUNCTION to_int( bool ) RETURNS int4 AS
'SELECT 1 WHERE $1 UNION SELECT 0 WHERE NOT $1';

Another approach would be the following:

CREATE FUNCTION only_true( bool ) RETURNS bool AS
'SELECT $1 WHERE $1 IS NOT NULL AND $1';

This would return true when the argument is true, null in all other cases.
You can use this with count rather than sum.

Of course, you could decide that you would fill the flagnew with either
true or null, instead of true and false. If you do, you could do a count
without the above function. You could get the true/false by asking whethe
flagnew IS NOT NULL.

Finally, the UNION is the way to go when you want to implement an outer
join, but using NOT IN is not recommended, because it is inefficient. A
better subquery would be:

NOT EXIST (
   SELECT  * FROM usermail
   WHERE loginid='michael'
     AND folder = folderid
)

Herouth

--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma





^ permalink  raw  reply  [nested|flat] 2+ messages in thread


end of thread, other threads:[~1999-07-14 09:43 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-07-13 23:31 counting bools in a complex query Michael Richards <miker@scifair.acadiau.ca>
1999-07-14 09:43 ` Herouth Maoz <herouth@oumail.openu.ac.il>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox