Counting boolean values (how many true, how many false)

On Tuesday 16 November 2010 8:23:16 am Alexander Farber wrote:

Hello,

if I have this table with 3 boolean columns:

# \d pref_rate
Table "public.pref_rep"
Column | Type | Modifiers
------------+-----------------------------+---------------
id | character varying(32) |
author | character varying(32) |
good | boolean |
fair | boolean |
nice | boolean |
about | character varying(256) |
last_rated | timestamp without time zone | default now()
Foreign-key constraints:
"pref_rate_author_fkey" FOREIGN KEY (author) REFERENCES pref_users(id)
"pref_rate_id_fkey" FOREIGN KEY (id) REFERENCES pref_users(id)

- how can I please count the number of
true's and false's for each column for a certain id?
(to find that persons rating)

I'm trying:

select sum(fair=true), sum(fair=false) from pref_rep;

but sum() doesn't like a boolean as an argument.

I've only come up with

select count(*) from pref_rep where fair=true and id='XXX';

but this would mean I have to call this line 6 times? (2 x column).

Thank you
Alex

test=> SELECT * from bool_test;
ifd | bool_fld
-----+----------
1 | f
1 | f
1 | f
1 | t
5 | f
98 | t
39 | f
30 | t
39 | t
30 | t
16 | f
(11 rows)

test=> SELECT bool_fld,case when bool_fld=true then count(bool_fld) else
count(bool_fld) end from bool_test where ifd=1 group by bool_fld ;
bool_fld | count
----------+-------
f | 3
t | 1
(2 rows)

--
Adrian Klaver
adrian.klaver@gmail.com

Hi,

sum doesn't like booleans, but it does like integers so:
sum(boolval::int) solves that problem for you.

SELECT id,sum(good::int + fair::int + nice::int) would get you a total
of the three for each row

good luck,
Maarten

On 16 November 2010 16:49, maarten <maarten.foque@edchq.com> wrote:

Hi,

sum doesn't like booleans, but it does like integers so:
sum(boolval::int) solves that problem for you.

SELECT id,sum(good::int + fair::int + nice::int) would get you a total
of the three for each row

good luck,
Maarten

Or, if you want a more flexible solution, you could try this:

CREATE OR REPLACE FUNCTION countif_add(current_count int, expression bool)
RETURNS int AS
$BODY$
BEGIN
IF expression = true THEN
RETURN current_count + 1;
ELSE
RETURN current_count;
END IF;
END;
$BODY$
LANGUAGE plpgsql

CREATE AGGREGATE countif (boolean)
(
sfunc = countif_add,
stype = int,
initcond = 0
);

Then you can call:

SELECT countif(fair) AS 'total fair', countif(!fair)
AS 'total unfair'
FROM pref_rep;

But it also would let you do stuff like:

SELECT countif(my_column > 3) AS 'stuff greater than 3',
countif(this_column = that_column) AS 'balanced values' FROM my_table;

--
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935

On 16 November 2010 17:02, Thom Brown <thom@linux.com> wrote:

On 16 November 2010 16:49, maarten <maarten.foque@edchq.com> wrote:

Hi,

sum doesn't like booleans, but it does like integers so:
sum(boolval::int) solves that problem for you.

SELECT id,sum(good::int + fair::int + nice::int) would get you a total
of the three for each row

good luck,
Maarten

Or, if you want a more flexible solution, you could try this:

CREATE OR REPLACE FUNCTION countif_add(current_count int, expression bool)
RETURNS int AS
$BODY$
BEGIN
       IF expression = true THEN
               RETURN current_count + 1;
       ELSE
               RETURN current_count;
       END IF;
END;
$BODY$
LANGUAGE plpgsql

CREATE AGGREGATE countif (boolean)
(
   sfunc = countif_add,
   stype = int,
   initcond = 0
);

Then you can call:

SELECT countif(fair) AS 'total fair', countif(!fair)
AS 'total unfair'
FROM pref_rep;

Correction here... you can't use !boolean... it would need to be...

SELECT countif(fair) AS 'total fair', countif(not fair) AS 'total unfair'

--
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935

Date: Tue, 16 Nov 2010 17:23:16 +0100
Subject: [GENERAL] Counting boolean values (how many true, how many false)
From: alexander.farber@gmail.com
To: pgsql-general@postgresql.org

Hello,

if I have this table with 3 boolean columns:

# \d pref_rate
Table "public.pref_rep"
Column | Type | Modifiers
------------+-----------------------------+---------------
id | character varying(32) |
author | character varying(32) |
good | boolean |
fair | boolean |
nice | boolean |
about | character varying(256) |
last_rated | timestamp without time zone | default now()
Foreign-key constraints:
"pref_rate_author_fkey" FOREIGN KEY (author) REFERENCES pref_users(id)
"pref_rate_id_fkey" FOREIGN KEY (id) REFERENCES pref_users(id)

- how can I please count the number of
true's and false's for each column for a certain id?
(to find that persons rating)

I'm trying:

select sum(fair=true), sum(fair=false) from pref_rep;

but sum() doesn't like a boolean as an argument.

I've only come up with

select count(*) from pref_rep where fair=true and id='XXX';

but this would mean I have to call this line 6 times? (2 x column).

Hi,

You can use a 'sum()' with 'case when':

select
sum(case when fair then 1 else 0 end) as fair,
sum(case when good then 1 else 0 end) as good,
sum(case when nice then 1 else 0 end)
from public.pref_rep;

Thank you all, I've ended up with the following.

But I really wonder if using boolean in my case
(trying to offer players a web from with 3 x 2 radio buttons
to rate each other) is really the best choice -
since it feels a bit awkward (and maybe slow?)

# create table pref_rep (
id varchar(32) references pref_users(id) check (id != author),
author varchar(32) references pref_users(id),
good boolean,
fair boolean,
nice boolean,
about varchar(256),
last_rated timestamp default current_timestamp
);

# select * from pref_rep ;
id | author | good | fair | nice | about | last_rated
--------+--------+------+------+------+-----------+----------------------------
DE7085 | DE7317 | t | t | t | comment 1 | 2010-11-16 20:26:04.780827
DE7085 | DE7336 | t | | t | comment 1 | 2010-11-16 20:26:14.510118
DE7085 | DE7641 | t | f | t | comment 2 | 2010-11-16 20:26:29.574055
DE7085 | DE7527 | f | f | t | comment 3 | 2010-11-16 20:26:45.211207
DE7085 | DE7184 | f | f | f | comment 3 | 2010-11-16 20:26:56.30616
(5 rows)

# select
sum(case when good then 1 else 0 end) as good,
sum(case when not good then 1 else 0 end) as "not good",
sum(case when fair then 1 else 0 end) as fair,
sum(case when not fair then 1 else 0 end) as "not fair",
sum(case when nice then 1 else 0 end) as nice,
sum(case when not nice then 1 else 0 end) as "not nice"
from public.pref_rep;

good | not good | fair | not fair | nice | not nice
------+----------+------+----------+------+----------
3 | 2 | 1 | 3 | 4 | 1
(1 row)

On Tue, Nov 16, 2010 at 11:32 AM, Alexander Farber
<alexander.farber@gmail.com> wrote:

sum(case when good then 1 else 0 end) as good,
sum(case when not good then 1 else 0 end) as "not good",
sum(case when fair then 1 else 0 end) as fair,
sum(case when not fair then 1 else 0 end) as "not fair",
sum(case when nice then 1 else 0 end) as nice,
sum(case when not nice then 1 else 0 end) as "not nice"
from public.pref_rep;

Here is one slightly more compact.
# select
COUNT(NULLIF( good, FALSE)) as good,
COUNT(NULLIF( good, TRUE)) as "not good",
COUNT(NULLIF( fair, FALSE)) as fair,
COUNT(NULLIF( fair, TRUE)) as "not fair",
COUNT(NULLIF( nice, FALSE)) as nice,
COUNT(NULLIF( nice, TRUE)) as "not nice",
from public.pref_rep;

--
Regards,
Richard Broersma Jr.

Visit the Los Angeles PostgreSQL Users Group (LAPUG)
http://pugs.postgresql.org/lapug

If you want to use the boolean approach I would just (as suggested earlier) cast to integer and sum. Like:
SELECT
sum(good::int) as good,
count(good)-sum(good::int) as "not good"

and so on

I thing the boolean approach seems reasonable if good, nice and fair is three separaty judgements as I understand they are.

Regards
Nicklas

----- Original message -----

sum(case when fair then 1 else 0 end) as fair,
=>
sum(case when fair then 1 end) as fair,

:)

regards,

Marc Mamin