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

From: Alexander Farber <alexander(dot)farber(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Counting boolean values (how many true, how many false)
Date: 2010-11-16 16:23:16
Message-ID: AANLkTi=eGWh4HVV67Qvb1mNazPFRyxL0x5nAxaWSJSKR@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Adrian Klaver 2010-11-16 16:41:13 Re: Counting boolean values (how many true, how many false)
Previous Message Michael Gould 2010-11-16 16:01:11 Re: Postgres forums ... take 2