From: | Nicklas Avén <nicklas(dot)aven(at)jordogskog(dot)no> |
---|---|
To: | Alexander Farber <alexander(dot)farber(at)gmail(dot)com>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: Counting boolean values (how many true, how many false) |
Date: | 2010-11-16 21:23:18 |
Message-ID: | 1289942598.2545.6.camel@Nokia-N900-51-1 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
From | Date | Subject | |
---|---|---|---|
Next Message | Christine Penner | 2010-11-16 21:27:34 | function problems |
Previous Message | Brent Wood | 2010-11-16 20:53:37 | Re: median for postgresql 8.3 |