|
> Date: Tue, 16 Nov 2010 17:23:16 +0100 > Subject: [GENERAL] Counting boolean values (how many true, how many false) > From: alexander.farber@xxxxxxxxx > To: pgsql-general@xxxxxxxxxxxxxx > > 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; |
