Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux