Обсуждение: Counting boolean values (how many true, how many false)
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
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 Tue, 2010-11-16 at 17:23 +0100, 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 >
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 -----
> 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)
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
sum(case when fair then 1 else 0 end) as fair, => sum(case when fair then 1 end) as fair, :) regards, Marc Mamin