On 16 November 2010 17:02, Thom Brown <t...@linux.com> wrote: > On 16 November 2010 16:49, maarten <maarten.fo...@edchq.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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
