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;
But it also would let you do stuff like:
SELECT countif(my_column > 3) AS 'stuff greater than 3',
countif(this_column = that_column) AS 'balanced values' FROM my_table;
--
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
