From: | André Fernandes <andre(dot)de(dot)camargo(dot)fernandes(at)hotmail(dot)com> |
---|---|
To: | <alexander(dot)farber(at)gmail(dot)com>, <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Counting boolean values (how many true, how many false) |
Date: | 2010-11-16 17:09:34 |
Message-ID: | COL112-W2F30A7298763974CD9E088A370@phx.gbl |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
> Date: Tue, 16 Nov 2010 17:23:16 +0100
> Subject: [GENERAL] Counting boolean values (how many true, how many false)
> From: alexander(dot)farber(at)gmail(dot)com
> To: pgsql-general(at)postgresql(dot)org
>
> 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;
From | Date | Subject | |
---|---|---|---|
Next Message | Vick Khera | 2010-11-16 17:09:46 | AfterTriggerSaveEvent() called outside of query |
Previous Message | maarten | 2010-11-16 17:05:21 | median for postgresql 8.3 |