Re: Counting boolean values (how many true, how many false) - Mailing list pgsql-general

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@gmail.com