From: | Alexander Farber <alexander(dot)farber(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Counting boolean values (how many true, how many false) |
Date: | 2010-11-16 19:32:33 |
Message-ID: | AANLkTingxv2CP_n_VfpgKL8Y0NcRyuAr=Peh1-mAMJwr@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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)
From | Date | Subject | |
---|---|---|---|
Next Message | Richard Broersma | 2010-11-16 19:49:01 | Re: Counting boolean values (how many true, how many false) |
Previous Message | Stephen Cook | 2010-11-16 19:22:58 | Re: Postgres forums ... take 2 |