From: Herouth Maoz <herouth@oumail.openu.ac.il>
To: Michael Richards <miker@scifair.acadiau.ca>
To: pgsql-sql@postgreSQL.org
Subject: Re: [SQL] counting bools in a complex query
Date: Wed, 14 Jul 1999 12:43:32 +0300
Message-ID: <l03130300b3b20a47eee5@[147.233.159.109]> (raw)
In-Reply-To: <Pine.BSF.4.10.9907132029580.38362-100000@scifair.acadiau.ca>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-sql@postgresql.org
Cc: herouth@oumail.openu.ac.il, miker@scifair.acadiau.ca
Subject: Re: [SQL] counting bools in a complex query
In-Reply-To: <l03130300b3b20a47eee5@[147.233.159.109]>
References: <Pine.BSF.4.10.9907132029580.38362-100000@scifair.acadiau.ca> <l03130300b3b20a47eee5@[147.233.159.109]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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