From: | Thom Brown <thom(at)linux(dot)com> |
---|---|
To: | maarten <maarten(dot)foque(at)edchq(dot)com> |
Cc: | Alexander Farber <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:05:07 |
Message-ID: | AANLkTi=g8JchGUO6yE6x1+j9vq7HBVODogv_GfjS9PgQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On 16 November 2010 17:02, Thom Brown <thom(at)linux(dot)com> wrote:
> On 16 November 2010 16:49, maarten <maarten(dot)foque(at)edchq(dot)com> wrote:
>> Hi,
>>
>> sum doesn't like booleans, but it does like integers so:
>> sum(boolval::int) solves that problem for you.
>>
>> SELECT id,sum(good::int + fair::int + nice::int) would get you a total
>> of the three for each row
>>
>> good luck,
>> Maarten
>
> Or, if you want a more flexible solution, you could try this:
>
> CREATE OR REPLACE FUNCTION countif_add(current_count int, expression bool)
> RETURNS int AS
> $BODY$
> BEGIN
> IF expression = true THEN
> RETURN current_count + 1;
> ELSE
> RETURN current_count;
> END IF;
> END;
> $BODY$
> LANGUAGE plpgsql
>
> CREATE AGGREGATE countif (boolean)
> (
> sfunc = countif_add,
> stype = int,
> initcond = 0
> );
>
> Then you can call:
>
> SELECT countif(fair) AS 'total fair', countif(!fair)
> AS 'total unfair'
> FROM pref_rep;
Correction here... you can't use !boolean... it would need to be...
SELECT countif(fair) AS 'total fair', countif(not fair) AS 'total unfair'
--
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935
From | Date | Subject | |
---|---|---|---|
Next Message | maarten | 2010-11-16 17:05:21 | median for postgresql 8.3 |
Previous Message | Thom Brown | 2010-11-16 17:02:32 | Re: Counting boolean values (how many true, how many false) |