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